행위

힌트 종류

DB CAFE

1 Oracle 힌트

1.1 힌트의 종류 별 분류

1.1.1 Optimization Goals and Approaches

  1. ALL_ROWS 혹은 FIRST_ROWS
  2. CHOOSE
  3. RULE

1.1.2 Acess Method Hints

            AND_EQUAL
            CLUSTER
            FULL
            HASH
            INDEX 혹은 NO_INDEX
            INDEX_ASC 혹은 INDEX_DESC
            INDEX_COMBINE
            INDEX_FFS
            ROWID

1.1.3 Join Order Hints

            ORDERED
            STAR

1.1.4 Join Operation Hints

            DRIVING_SITE
            HASH_SJ, MERGE_SJ 혹은 NL_SJ
            LEADING
            USE_HASH 혹은 USE_MERGE
            USE_NL

1.1.5 Parallel Execution Hints

            PARALLEL 혹은 NOPARALLEL
            PARALLEL_INDEX
            PQ_DISTRIBUTE
            NOPARALLEL_INDEX

1.1.6 Query Transformation Hints

            EXPAND_GSET_TO_UNION
            FACT 혹은 NOFACT
            MERGE
            NO_EXPAND
            NO_MERGE
            REWIRTE 혹은 NOREWRITE
            STAR_TRANSFORMATION
            USE_CONCAT

1.1.7 Other Hints

            APPEND 혹은 NOAPPEND
            CACHE 혹은 NOCACHE
            CURSOR_SHARED_EXACT
            DYNAMIC_SAMPLING
            NESTED_TABLE_GET_REFS
            UNNEST 혹은 NO_UNNEST
            ORDERED_PREDICATES
 

1.2 힌트의 설명 및 사용법

1.2.1 ALL_ROWS

/*+ ALL_ROWS */

  • 최소한의 자원을 사용하여 결과값의 전체를 추출하게 합니다.
  1. ALL_ROWS 힌트는 최소 총 리소스 소비 인 최상의 처리량을 목표로 문 블록을 최적화하도록 최적화 프로그램에 지시합니다.
    1. 예를 들어 최적화 프로그램은 쿼리 최적화 접근 방식을 사용하여 최상의 처리량을 위해이 문을 최적화합니다.
  2. SQL 문에 ALL_ROWS 또는 FIRST_ROWS 힌트를 지정하고 데이터 딕셔너리에 액세스 한 테이블에 대한 통계가없는 경우 옵티마이저는 이러한 테이블에 할당 된 스토리지와 같은 기본 통계 값을 사용하여 누락된 통계 및 이후에 실행 계획을 선택합니다.
    1. 이러한 추정치는 DBMS_STATS 패키지에서 수집 한 것만 큼 정확하지 않을 수 있으므로 DBMS_STATS 패키지를 사용하여 통계를 수집해야합니다.
  3. ALL_ROWS 또는 FIRST_ROWS 힌트와 함께 액세스 경로 또는 조인 작업에 대한 힌트를 지정하면 최적화 프로그램이 힌트에 지정된 액세스 경로 및 조인 작업에 우선 순위를 부여합니다.

1.2.2 AND_EQUAL

/*+ AND_EQUAL (table index index [index] [index] [index] ) */

  • 복수의 단일 컬럼을 스캔하여 머지 방식으로 처리하게 합니다.

1.2.3 APPEND HINT

/*+ APPEND */

  1. 시리얼모드 데이터베이스에서 Direct INSERT를 실행하게 합니다.
  2. Enterprise Edition 이 아닌 데이터베이스의 기본 모드는 시리얼 모드입니다. 이러한 직렬 모드 데이터 베이스에서의 INSERT 작업은 Conventional를 기본값으로 하고 병렬 처리 시에는 Direct INSERT를 기본값으로 합니다.
  1. APPEND 힌트는 최적화 프로그램이 직접 경로 INSERT를 사용하도록 지시합니다.
  2. 일반 INSERT는 시리얼 모드의 기본값입니다. 시리얼 모드에서는 APPEND 힌트를 포함하는 경우에만 다이렉트 패스를 사용할 수 있습니다.
  3. 패러럴모드에서 다이렉트 패스INSERT는 기본값입니다. 병렬 모드에서는 NOAPPEND 힌트를 지정한 경우에만 일반 INSERT을 사용할 수 있습니다.
  4. INSERT가 병렬로 진행 될지 여부는 APPEND 힌트와 무관합니다.
  5. 다이렉트 패스INSERT에서 데이터는 현재 테이블에 할당 된 기존 공간을 사용하지 않고 테이블 끝에 추가됩니다. 결과적으로 다이렉트 패스 INSERT는 기존 INSERT보다 훨씬 빠를 수 있습니다.

1.2.4 CACHE HINT

cache_hint.gif

/*+ CACHE (table) +/

  • FULL 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 MRU 쪽에 위치시킵니다. 작은 테이블의 사용 시 유용합니다.
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
  FROM employees hr_emp;
  1. CACHE 및 NOCACHE 힌트는 V$SYSSTAT 데이터 딕셔너리 뷰에 표시된대로 시스템 통계 테이블 스캔 (롱 테이블) 및 테이블 스캔 (숏 테이블)에 영향을줍니다.

1.2.5 CHOOSE HINT

/*+ CHOOSE +/

  • Rule-Based 와 Cost-Based 방식 간의 선택을 유도합니다. 선택 기준은 사용 객체의 분석 정보 존재 여부이며, 사용되는 객체들중 하나라도 분석 정보가 존재한다면 Cost-Based 방식을 사용하게 됩니다.

1.2.6 CLUSTER HINT

/*+ CLUSTER (table) +/

  • 지정 테이블의 클러스터 스캔을 유도합니다. 클러스터된 객체에만 사용할 수 있습니다.

1.2.7 CURSOR_SHARING_EXACT

/*+ CURSOR_SHARING_EXACT +/

  • 바인드 변수 값의 교체를 불가능하게 합니다.
  • 기본적으로 CURSOR_SHARING 파라미터를 사용하여, 안전하다고 판단될 시 SQL 내의 바인드 변수 값을 교체할 수 있게 되어 있습니다.
  1. Oracle은 안전한 경우 SQL 문의 리터럴을 바인드 변수로 바꿀 수 있습니다.
  2. 이 교체는 CURSOR_SHARING 초기화 매개 변수로 제어됩니다.
    1. CURSOR_SHARING_EXACT 힌트는 최적화 프로그램에이 동작을 해제하도록 지시합니다.
    2. 이 힌트를 지정하면 Oracle은 리터럴을 바인드 변수로 바꾸지 않고 SQL 문을 실행합니다.

1.2.8 DRIVING_SITE

driving_site_hint.gif

/*+ DRIVING_SITE (table) +/

  • 오라클이 선택한 SITE 대신, 지정한 SITE를 사용하여 쿼리를 실행합니다. Rule-Based 와 Cost-Based, 두 모드 다 사용 가능합니다.
SELECT /*+ DRIVING_SITE(departments) */ * 
  FROM employees, departments@rsite 
  WHERE employees.department_id = departments.department_id;

이 쿼리가 힌트없이 실행되면 departments의 ROW가 로컬 사이트로 전송되고 여기에서 조인이 실행됩니다. 힌트를 사용하면 employees의 ROW가 원격 사이트로 전송되고 쿼리가 실행되고 결과 집합이 로컬 사이트로 반환됩니다.

1.2.9 DYNAMIC_SAMPLING

/*+ DYNAMIC_SAMPLING ( [table] n ) +/

  • 해당 객체의 Selectivity 와 Cardinality 에 대한 보다 자세한 정보를 자동으로 생성시켜 실행합니다.
  • 값은 0 부터 10 까지 지정할 수 있으며, 높을 수록 보다 자세한 정보를 생성하게 됩니다. 테이블에 해당 값을 지정하지 않았을 경우, 기본 값은 CURSOR 레벨의 값이 쓰여집니다.

1.2.10 EXPAND_GSET_TO_UNION

/*+ EXPAND_GSET_TO_UNION +/

  • GROUP BY GROUPING SET 혹은 GROUP BY ROLLUP 등과 같은 구문을 포함하는 쿼리에 사용할 수 있습니다.
  • 이 힌트는 기존의 쿼리를 개별적인 그룹 생성 후, UNION ALL 방식으로 실행되게 유도합니다.

1.2.11 FACT HINT

/*+ FACT (table) +/

  • 스타 변형 구문에서 사용되며 해당 테이블이 FACT 테이블로 사용되게 유도합니다.

1.2.12 FIRST_ROWS

/*+ FIRST_ROWS (n) +/

  1. FIRST_ROWS 힌트는 Oracle이 빠른 응답을 위해 개별 SQL 문을 최적화하도록 지시하여 처음 n 개 행을 가장 효율적으로 반환하는 계획을 선택합니다.
  2. 정수의 경우 반환 할 행 수를 지정합니다.
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE department_id = 20;

이 예에서 각 부서에는 많은 직원이 있습니다. 사용자는 부서 20의 처음 10 명의 직원이 가능한 빨리 표시되기를 원합니다.

  1. 옵티마이져는 DELETE 및 UPDATE 문 블록과 정렬 또는 그룹화와 같은 블록킹 명령을 포함하는 SELECT문 블록에서 이 힌트를 무시합니다.
  2. Oracle Database는 첫 번째 행을 반환하기 전에 명령문에서 액세스 한 모든 행을 검색해야하므로 이러한 명령문은 최상의 응답 시간을 위해 최적화 할 수 없습니다.
  3. 이러한 명령문에이 힌트를 지정하면 데이터베이스가 최상의 처리량을 위해 최적화됩니다.

1.2.13 FULL HINT

/*+ FULL (table) */

  • 지정한 테이블에 대해 풀 테이블 스캔을 유도합니다.

1.2.14 HASH HINT

hash_hint.gif

/*+ HASH (table) */

  • 지정한 테이블에 대해 hash 스캔을 수행하도록 유도합니다.
  • 클러스터 테이블 만을 대상으로 합니다.

1.2.15 HASH_AJ

/*+ HASH_AJ */

  • EXISTS 구문 뒤에 오는 서브 쿼리에 사용되며 HASH_SJ, MERGE_SJ 혹은 NL_SJ 등을 사용할 수 있습니다.
  • HASH_SJ 은 hash semi-join 이고, MERGE_SJ 은 sort merge semi-join 이며 NL_SJ 은 nested loop semi-join 입니다.

1.2.16 INDEX

index_hint.gif

/*+ INDEX (table index [index] [index] ... ) */

  • 지정한 테이블의 인덱스 스캔을 실행하도록 유도합니다.
  • Domain, B-tree, bitmap, bitmap join 인덱스 등이 사용될 수 있으나, bitmap 인덱스 들의 사용 시, INDEX 힌트보다는 INDEX_COMBINE 힌트 사용이 추천됩니다.
  1. INDEX 힌트는 지정된 테이블에 대해 인덱스 스캔을 사용하도록 최적화 프로그램에 지시합니다.
    1. 함수 기반, 도메인, B- 트리, 비트 맵 및 비트 맵 조인 인덱스에 대해 INDEX 힌트를 사용할 수 있습니다.
    2. 힌트의 동작은 indexspec 사양에 따라 다릅니다.
    3. INDEX 힌트가 사용 가능한 단일 인덱스를 지정하는 경우 데이터베이스는 인덱스에 대해 스캔을 수행합니다.
      1. 옵티마이저는 FULL 테이블 스캔이나 테이블의 다른 인덱스 스캔을 고려하지 않습니다.
    4. 여러 인덱스 조합에 대한 힌트의 경우 Oracle은 INDEX가 아닌 INDEX_COMBINE을 사용할 것을 권장합니다. 이는보다 다양한 힌트이기 때문입니다.
      1. INDEX 힌트가 사용 가능한 인덱스 목록을 지정하는 경우 옵티마이 저는 목록의 각 인덱스에 대한 스캔 비용을 고려한 다음 가장 낮은 비용으로 인덱스 스캔을 수행합니다.
      2. 데이터베이스는 이러한 액세스 경로의 비용이 가장 낮은 경우이 목록에서 여러 인덱스를 스캔하고 결과를 병합하도록 선택할 수도 있습니다.
      3. 데이터베이스는 FULL 테이블 스캔 또는 힌트에 나열되지 않은 인덱스에 대한 스캔을 고려하지 않습니다.
    5. INDEX 힌트가 인덱스를 지정하지 않으면 옵티마이 저는 테이블에서 사용 가능한 각 인덱스에 대한 스캔 비용을 고려한 다음 가장 낮은 비용으로 인덱스 스캔을 수행합니다.
      1. 데이터베이스는 이러한 액세스 경로의 비용이 가장 낮은 경우 여러 인덱스를 스캔하고 결과를 병합하도록 선택할 수 있습니다.
      2. 옵티마이저는 FULL 테이블 스캔을 고려하지 않습니다.

1.2.17 INDEX_ASC

/*+ INDEX_ASC (table [index] [index] ... ) +/

  • 해당 테이블의 인덱스를 순차적 방식으로 스캔하게 합니다.
  • 해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 값의 순차적 방식으로 읽게 됩니다.
  1. INDEX_ASC 힌트는 지정된 테이블에 대해 인덱스 스캔을 사용하도록 최적화 프로그램에 지시합니다.
    1. 명령문이 인덱스 범위 스캔을 사용하는 경우 Oracle 데이터베이스는 인덱스 된 값의 오름차순으로 인덱스 항목을 스캔합니다.
    2. 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다.
  2. 범위 스캔의 기본 동작은 색인화 된 값의 오름차순 또는 내림차순 색인의 경우 내림차순으로 색인 항목을 스캔하는 것입니다.
    1. 이 힌트는 인덱스의 기본 순서를 변경하지 않으므로 INDEX 힌트 이상을 지정하지 않습니다.
    2. 그러나 기본 동작이 변경되면 INDEX_ASC 힌트를 사용하여 오름차순 범위 스캔을 명시 적으로 지정할 수 있습니다.

1.2.18 INDEX_COMBINE

index_combine_hint.gif

/*+ INDEX_COMBINE (table [index] [index] ... ) +/

  • 해당 테이블에 Bitmap 인덱스의 존재 시, Bitmap 인덱스를 통한 액세스를 유도합니다.
  • 힌트 내에 인덱스의 이름이 쓰여지지 않을 시, 해당 인덱스의 Boolean 값을 사용하여 최적의 Cost를 산출하여 실행하게 됩니다.
  1. INDEX_COMBINE 힌트는 옵티마이저가 테이블에 비트 맵 액세스 경로를 사용하도록 지시합니다.
  2. INDEX_COMBINE 힌트에서 indexspec을 생략하면 옵티마이저는 테이블에 대한 최적의 비용 추정치가있는 인덱스의 Boolean 콤비네이션을 사용합니다.
  3. indexspec을 지정하면 옵티마이저는 지정된 인덱스의 일부 Boolean 콤비네이션을 사용하려고합니다.
  4. 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다. 예를 들면 :
SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;

1.2.19 INDEX_DESC

/*+ INDEX_DESC (table [index] [index] ... ) +/

  • 지정한 인덱스에 대해 인덱스 스캔을 역순으로 실행합니다.
  • 해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 컬럼의 값을 사용하여 역순으로 실행합니다.
  • 파티션 인덱스에서는 파티션 별 개별적인 실행이 이루어집니다.

1.2.20 INDEX_FFS

index_ffs_hint.gif

/*+ INDEX_FFS (table [index] [index] ... ) +/ /*+ NO_INDEX_FFS */

  • Full 테이블 스캔 대신에 빠른 인덱스 패스트 풀스캔의 실행을 유도합니다.
  • 멀티 블럭 I/O로 처리함
  • 고려할 사항
 인덱스 Fast Full Scan은 인덱스에 의해 정렬된 데이터가 추출되지 않는다.
 인덱스 Fast Full Scan만  병렬 프로세싱이 가능하다.
  • 언제 사용하는지?
 인덱스로만 원하는 데이터를 모두 추출하는 경우
 해당 테이블의 데이터 중 대부분을 추출하는 경우
  • BETWEEN,부등호 연산, COUNT, MAX, MIN 질의를 할 때 성능상의 이점
  • 정렬이 불필요한 경우

1.2.21 INDEX_JOIN

index_join_hint.gif

  1. INDEX_JOIN 힌트는 옵티마이저에게 인덱스 조인을 액세스 경로로 사용하도록 지시합니다.
  2. 힌트가 긍정적인 효과를 내려면 쿼리를 해결하는 데 필요한 모든 열을 포함하는 충분히 적은 수의 인덱스가 있어야합니다.
  3. 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다.
    1. 예를 들어 다음 쿼리는 인덱스 조인을 사용하여 manager_id 및 department_id 열에 액세스합니다. 두 열은 모두 employee 테이블에 인덱싱됩니다.
SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
  FROM employees e
  WHERE manager_id < 110
    AND department_id < 50;

1.2.22 INDEX_SS

index_ss_hint.gif

  1. INDEX_SS 힌트는 지정된 테이블에 대해 인덱스 건너 뛰기 스캔을 수행하도록 최적화 프로그램에 지시합니다.
  2. 명령문이 인덱스 range scan을 사용하는 경우 Oracle은 인덱스 값의 오름차순으로 인덱스 항목을 스캔합니다.
  3. 파티션된 인덱스에서 결과는 각 파티션 내에서 오름차순으로 표시됩니다.
SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';


1.2.23 INDEX_SS_ASC

index_ss_asc_hint.gif


1.2.24 INDEX_SS_DESC

index_ss_desc_hint.gif

1.2.25 LEADING_HINT

merge_hint.gif

/*+ LEADING (table) +/

  • 테이블 간의 조인 시에 지정한 테이블을 먼저 수행하도록 유도합니다.
  • 두 개 이상의 LEADING 힌트의 사용 시, 힌트 자체가 사용되어 지지 않습니다.
  • ORDERED 힌트와 더불어 사용시, LEADING 힌트는 사용되지 않습니다.
  1. LEADING 힌트는 지정된 테이블이 조인 그래프의 종속성으로 인해 지정된 순서대로 먼저 조인 될 수없는 경우 무시됩니다.
  2. 두 개 이상의 충돌하는 LEADING 힌트를 지정하면 모두 무시됩니다.
  3. ORDERED 힌트를 지정하면 모든 LEADING 힌트를 재정의합니다.

1.2.26 MERGE

merge_hint.gif

/*+ MERGE (table) +/

  • 각 쿼리의 결과값을 머지합니다.
  • 해당 쿼리 내에 GROUP BY 절의 사용 이나 SELECT 구문에 DISTINCT 가 사용되었을 시, 머지의 실행이 가능할 경우에만 힌트가 실행됩니다.
  • IN 과 서브 쿼리의 사용 시, 서브 쿼리와 상위 쿼리 간의 상호 관계가 없을 때에만 머지의 실행이 가능합니다.
  • 이 힌트는 Cost-based 가 아닙니다. 따라서 액세스하는 실행 쿼리 블럭에 MERGE 힌트가 반드시 명시되어야만 합니다. 그렇지 않을 경우 옵티마이저는 다른 실행 계획을 수립합니다.
SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
   FROM employees e1,
   (SELECT department_id, avg(salary) avg_salary 
      FROM employees e2
      GROUP BY department_id) v 
   WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
  1. 뷰의 쿼리 블록이 SELECT 목록에 GROUP BY 절 또는 DISTINCT 연산자를 포함하는 경우 옵티마이 저는 복합 뷰 병합이 활성화 된 경우에만 뷰를 액세스 문에 병합 할 수 있습니다.
    1. 복합 병합은 하위 쿼리가 상관되지 않은 경우 IN 하위 쿼리를 액세스 문에 병합하는 데 사용할 수도 있습니다.

1.2.27 MERGE_AJ

            HASH_AJ 를 참조하십시요.

1.2.28 MERGE_SJ

            HASH_AJ 를 참조하십시요.

1.2.29 NL_AJ

            HASH_AJ 를 참조하십시요.

1.2.30 NL_SJ

            HASH_AJ 를 참조하십시요.

1.2.31 NOAPPEND

/*+ NOAPPEND +/

  • 병럴 모드에서의 INSERT 작업을 Conventional 방식으로 수행합니다.
  • 병렬 모드에서는 Direct-path INSERT 가, 직렬 모드에서는 Conventional INSERT가 기본값입니다.

1.2.32 NOCACHE

/*+ NOCACHE (table) +/

  • 풀 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 LRU 쪽에 위치시킵니다. 기본 모드입니다.

1.2.33 NO_EXPAND

/*+ NO_EXPAND +/

  • 실행 쿼리 내에 OR 나 WHERE 절의 IN 이 사용되었을 시, Cost-Based 옵티마이저가 쿼리 처리를위해 OR 를 사용한 확장을 사용하는 것을 방지합니다.
  • 일반적으로 옵티마이저는 위와 같은 경우 OR – 확장의 가격이 확장을 사용하지 않는 것보다 적을 시, 확장 방식으로 수행합니다.

1.2.34 NO_FACT

/*+ NO_FACT (table) +/

  • Star 변형 시, 해당 테이블의 FACT 테이블로서의 사용을 방지합니다.

1.2.35 NO_INDEX

/*+ NO_INDEX (table [index] [index] ... ) +/

  • 지정 테이블의 인덱스 사용을 방지합니다.

1.2.36 NO_MERGE

no_merge_hint.gif

/*+ NO_MERGE (table) +/

  • 머지 처리 방식의 사용을 방지합니다.
  1. NO_MERGE 힌트는 외부 쿼리와 인라인 뷰 쿼리를 단일 쿼리로 결합하지 않도록 최적화 프로그램에 지시합니다.
  2. 이 힌트를 사용하면 뷰에 액세스하는 방식에 더 많은 영향을 미칠 수 있습니다.
  3. 예를 들어 다음 문은 seattle_dept보기가 병합되지 않도록합니다. :
SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name 
  FROM employees e1, 
    (SELECT location_id, department_id, department_name 
       FROM departments 
       WHERE location_id = 1700) seattle_dept 
  WHERE e1.department_id = seattle_dept.department_id;
  1. 뷰 쿼리 블록에서 NO_MERGE 힌트를 사용하는 경우 인수없이 지정하십시오. 주변 쿼리에 NO_MERGE를 지정하는 경우 뷰 이름을 인수로 사용하여 지정합니다.

1.2.37 NOPARALLEL

/*+ NOPARALLEL (table) +/

  • 지정한 테이블의 병렬 처리를 방지합니다.
  • 테이블의 지정된 PARALLEL 값에 대해서 우선권을 가집니다.
  • 중첩 테이블에 대해서는 병렬 처리를 할 수 없습니다.

1.2.38 NOPARALLEL_INDEX

/*+ NOPARALLEL_INDEX (table [index] [index] ... ) +/

  • 인덱스 스캔 작업의 병렬 처리를 방지합니다.
  • 인덱스에 지정된 PARALLEL 값에 우선권을 가집니다.

1.2.39 NO_PUSH_PRED

/*+ NO_PUSH_PRED (table) +/

  • 결과값에 대한 조인 방식 서술의 강제적 수행을 방지합니다.

1.2.40 NO_PUSH_SUBQ

/*+ NO_PUSH_SUBQ +/

  • 서브 쿼리의 결과값을 머지하지 않는 실행 계획이 실행 계획 설립 단계에서 제일 마지막으로 참조되는 것을 방지합니다.
  • 일반적으로 서브 쿼리의 Cost 가 높거나, 처리 로우의 갯수를 크게 줄여주지 못할 때에는 서브 쿼리를 마지막에 참조하는 것이 성능 향상에 도움이 됩니다.


1.2.41 NO_QUERY_TRANSFORMATION

  1. NO_QUERY_TRANSFORMATION 힌트는 OR 확장, 뷰 병합, 하위 쿼리 중첩 해제, 스타 변환 및 구체화 된 뷰 재작성을 포함하되 이에 국한되지 않는 모든 쿼리 변환을 건너 뛰도록 옵티마이저에 지시합니다.
SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
  FROM (SELECT *
        FROM employees e) v
  WHERE v.last_name = 'Smith';

1.2.42 NO_REWRITE

/*+ NO_REWRITE +/

  • 해당 쿼리 블럭의 쿼리 재생성의 실행을 방지합니다.
  • QUERY_REWRITE_ENALBE 파라미터에 대해 우선권을 가집니다.
  • NOREWRITE 힌트의 사용 시, Function-Based 인덱스의 사용이 금지됩니다.(NOREWRITE 힌트는 더이상 사용 안함 11g 이상 NO_REWRITE)
  1. NO_REWRITE 힌트는 QUERY_REWRITE_ENABLED 매개 변수의 설정을 재정 의하여 쿼리 블록에 대한 쿼리 다시 쓰기를 비활성화하도록 최적화 프로그램에 지시합니다.

1.2.43 NO_UNNEST

/*+ NO_UNNEST +/

  • 해당 서브 쿼리 블럭의 UNNESTING 설정의 사용을 방지합니다.

1.2.44 ORDERED

/*+ ORDERED +/

  • FROM 절에 나열된 테이블의 순서대로 조인 작업을 실행합니다.
  1. ORDERED 힌트는 Oracle이 FROM 절에 나타나는 순서대로 테이블을 조인하도록 지시합니다. Oracle은 ORDERED 힌트보다 다재다능한 LEADING 힌트를 사용할 것을 권장합니다.
  1. 조인이 필요한 SQL 문에서 ORDERED 힌트를 생략하면 옵티마이 저는 테이블을 조인 할 순서를 선택합니다.
  2. 옵티마이저가 각 테이블에서 선택한 행 수에 대해 알지 못하는 것을 알고있는 경우 ORDERED 힌트를 사용하여 조인 순서를 지정할 수 있습니다. 이러한 정보를 사용하면 최적화 프로그램보다 내부 및 외부 테이블을 더 잘 선택할 수 있습니다.

1.2.45 ORDERED_PREDICATE

/*+ ORDERED_PREDICATE +/

  • 옵티마이저에 의한 조인 관계의 Cost를 산출하기 위해 미리 정해둔 조인 관계 별 실행 순서의 사용을 방지합니다.

n 인덱스 키를 사용한 조인 관계들은 제외됩니다.

  • 이 힌트는 쿼리의 WHERE 절에 사용하십시요.

1.2.46 OPT_PARAM

  1. OPT_PARAM 힌트를 사용하면 현재 쿼리 기간 동안 만 초기화 매개 변수를 설정할 수 있습니다.
  2. 이 힌트는 OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING 및 STAR_TRANSFORMATION_ENABLED 매개 변수에만 유효합니다.
    1. 예를 들어, 다음 힌트는 매개 변수가 추가되는 명령문에 대해 STAR_TRANSFORMATION_ENABLED 매개 변수를 TRUE로 설정합니다.
SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

1.2.47 PARALLEL

/*+ PARALLEL (table [ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/

  • 병렬 처리에 사용될 서버 프로세스의 갯수를 설정.
  • 병렬 처리 조건에 위배될 시, 힌트는 적용되지 않음.
  • TEMP테이블에 대한 PARALLEL_HINT 사용 시에는 적용되지 않음.


1.2.48 PARALLEL_INDEX

/*+ PARALLEL_INDEX (table [ [index] [, index]...] [ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/

  • 파티션 인덱스의 인덱스 범위 스캔 작업의 병렬 처리에 할당될 서버 프로세스의 갯수를 지정합니다.

1.2.49 PQ_DISTRIBUTE

/*+ PQ_DISTRIBUTE (table [,] outer_distribution, inner_distribution) +/

  • 병렬 조인 시, Producer 프로세스와 Consumer 프로세스 간의 데이터 전달 방식을 지정합니다.

1.2.50 PUSH_PRED

/*+ PUSH_PRED (table) +/ /*+ NO_PUSH_PRED */

  • 결과값에 대한 조인 방식 서술의 강제적 수행을 실행합니다.
  • 메인쿼리에서 서브쿼리를 제어
  • 메인쿼리에 기술
  • 조인조건을 인라인뷰 안으로 PUSH 하는 힌트


SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
   FROM employees e,
            ( SELECT manager_id
              FROM employees
            ) v
 WHERE e.manager_id = v.manager_id(+)
     AND e.employee_id = 100;

1.2.51 PUSH_SUBQ

/*+ PUSH_SUBQ +/ /*+ NO_PUSH_SUBQ */

  • unnest되지 않은 subquery를 최대한 빠르게 수행하여 main table이 다른 테이블과 조인하기 전에 필터하여 최대한 건수를 줄여줄 필요가 있을 때 사용하는 힌트.
  • 서브쿼리 조인조건을 메인쿼리에 먼저 조인처리
  • no_unnest 힌트와 같이 사용.
  • 실행 계획에서 가능한 빠른 단계에서 노머지(nomerge)된 서브 쿼리를 평가하기 위해 옵티마이저에 지시
  • 머지가 불가능한 서브 쿼리들의 우선 실행 계획을 실행 계획 수립시 먼저 참조하도록 지시
  • 서브 쿼리가 Remote 테이블,머지 조인의 사용 시 힌트는 실행되지 않음.
  • unnest란 subquery가 풀려서 main query와 조인 되는 것.
  • 작성자가 subquery 형태로 작성하더라도 optimizer는 우선적으로 unnest형태로 쿼리변환 시도.
  • unnest되지 않은 subquery를 조인형태로 풀어주게 되면 다양한 테이블 조인을 구상할 수 있게 되기 때문에 되도록 unnest를 하려고 하는 것.
  • 따라서 optimizer가 unnest 시도하지 않도록 no_unnest 힌트와 같이 사용되는 것.

1.2.52 REWRITE

/*+ REWRITE [ ( [materialized_view] [materialized_view]...) ] +/

  • 실행 계획의 가격에 상관없이 Materialized View 를 사용하여 쿼리 재생성을 하도록 합니다.
  • Materialized View 를 지정할 시, 지정한 Materialized View 의 가격에 상관없이 무조건 쿼리 재생성을 실행합니다.
  • Materialized View 를 지정하지 않을 시, 오라클은 사용 가능한 모든 Materialized View 를 참조하여 그 중 가장 가격이 낮은 Materialized View 를 사용하여 쿼리 재생성을 합니다.
  • Materialized View 를 지정하지 않는 힌트의 사용이 권장됩니다.

1.2.53 ROW_ID

/*+ ROWID (table) +/

  • 지정한 테이블의 스캔을 ROWID 방식으로 수행하게 합니다.

1.2.54 RULE

/*+ RULE +/

  • 실행 계획을 Rule-Based 방식으로 실행하게 합니다.
  • 해당 쿼리 블럭에 다른 힌트 또한 사용되었을 경우, 다른 힌트들은 사용되지 않습니다.

1.2.55 STAR

/*+ STAR +/

  • Star 쿼리 계획이 사용 가능하다면, 실행하게 합니다.
  • Star 쿼리 계획이란 가장 큰 테이블이 마지막 순서로 조인되며, 조인될 시 가장 큰 테이블 내의 Concatenated 인덱스에 대해 Nested Loop 조인 방식으로 실행되는 것을 말합니다.
  • 최소한 세개 이상의 테이블이 사용되며, 제일 큰 테이블의 Concatenated 인덱스의 생성에 최소한 세 개 이상의 컬럼이 사용되어야 하며, 액세스나 조인 방식에 충돌이 없어야만 이 힌트는 사용됩니다.

1.2.56 STAR_TRANSFORMATION

/*+ STAR_TRANSFORMATION +/

  • 옵티마이저가 Star 변형 작업에 최적화된 실행 계획을 수립, 실행하도록 합니다.
  • 힌트를 사용하지 않을 시, 옵티마이저는 일반적인 작업에 최적화된 실행 계획을 수행합니다.
  • 힌트를 사용하였어도 변형 작업에 맞추어진 실행 계획을 실행한다는 보장은 없습니다. 다른 일반적인 힌트의 사용과 마찬가지로 비교 분석 후, 오라클의 판단에 따라 다른 실행 계획이 실행될 수 있습니다.

1.2.57 UNNEST

  1. 서브쿼리를 메인쿼리로 몰아 내서 조인토록 유도하는 하는 힌트, 즉 중첩 해제 힌트
  2. 하위(서브) 쿼리(Sub Query)는 상위 쿼리문의 WHERE 절에 나타날 때 중첩 됨.
    1. Oracle Database가 중첩된 하위 쿼리가있는 문을 측정(평가)할 때 하위 쿼리 부분을 여러 번 평가해야하며 일부 효율적인 액세스 경로 또는 조인을 간과 할 수 있습니다.
  3. 서브쿼리 중첩해제(SubQuery UNNEST)는 서브쿼리의 본문을 포함하는 명령문의 본문에 중첩해제(Unnest) 와 병합(Merge) 하여 옵티마이저가 액세스 경로 및 조인을 평가할 때 함께 고려할 수 있도록합니다.
    1. 옵티마이저는 몇 가지 예외를 제외하고 대부분의 서브쿼리를 중첩 해제 할 수 있습니다.
    2. 예외는 ROWNUM 가상컬럼
    3. 집합 연산자 중 하나
    4. 중첩집계함수,서브쿼리의 외부(바깥) 쿼리 블록이 아닌 쿼리블록에 상호연관함수가 포함 된 계층적 서브쿼리 및 서브쿼리가 포함됩니다.
  4. 일반적으로 옵티마이저는 다음 조건을 경우 자동 중첩해제(UNNEST) 함
    1. 연관관계가 없는 IN 절의 하위 쿼리
    2. IN 과 EXISTS 의 연관된 서브쿼리, 집계 함수 또는 GROUP BY 절을 포함하지 않는 경우
  5. 옵티마이저에 추가서브쿼리 유형의 중첩을 해제하도록 지시하여 확장 서브쿼리 중첩해제를 활성화 할 수 있습니다.
    1. 서브쿼리에 HASH_AJ 또는 MERGE_AJ 힌트를 지정하여 상관되지 않은 NOT IN 서브쿼리의 중첩을 해제 할 수 있음.
    2. 서브쿼리에 UNNEST 힌트를 지정하여 다른 서브쿼리를 중첩 해제 할 수 있습니다.
  6. 반대로 NO_UNNEST 힌트는 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립되도록 하는 힌트임.
  7. WHERE절에 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 하며 IN, EXISTS 관계없이 메인쿼리에서 읽히는 FILTER방식으로 처리되어
메인레코드 하나 읽을 때마다 서브쿼리를 반복적으로 수행하면서 조건에 맞는 데이터를 추출하는 것이다. 
    1. 이러한 필터방식이 최적의 성능을 보장하지 않으므로 옵티마이저는 조인문으로 변경후 최적화(Unnesting) 하거나 메인과 서브쿼리를 별도의 SUB PLAN으로 분리하여 각각 최적화를 수행하는데 이때 서브쿼리에 FILTER 연산이 나타난다.
    2. 서브 쿼리를 Unnesting 하지 않는다면 메인쿼리의 건 마다 서브쿼리를 반복 수행하는 FILTER 연산자를 사용하기에
 Unnesting 힌트는 효율적으로 사용한다면 성능 향상을 가져온다.

/*+ UNNEST +/

  • 서브 쿼리에 기술
  • 서브 쿼리 블럭에 대해 인증성 만을 검사하게 합니다.
  • 인증이 되었다면 그 이상의 검증 작업없이 서브쿼리에 대한 UNNESTING 의 설정을 가능하게 합니다.
select *
  from emp
  where deptno in (
                    select /*+ unnest */ deptno
                    from dept
                  );

/*+ NO_UNNEST +/

1.2.58 USE_CONCAT

/*+ USE_CONCAT +/

  • WHERE 절의 OR 조인 을 UNION ALL 로 변경하여 수행하게 합니다.
  • 일반적으로 이러한 변경은 결과값의 병합 수행의 가격이 수행하지 않을 시의 가격 보다 낮을 때에만 실행됩니다.

1.2.59 USE_HASH

/*+ USE_HASH (table [table]...) +/ /*+ NO_USE_HASH */

  • Hash 조인 방식으로 각 테이블을 조인.
  • EQUAL JOIN 에서만 가능
  • 작은테이블과 큰테이블 조인시 작은테이블이 드라이빙 테이블에 좋음
  • ORDERED힌트를 사용하여 조인순서 조정하면 좋음
  • HASH AREA SIZE 확인: 작은테입블사이즈 * 1.6 적당
  • 각 테이블 에 대해 1 번만 찾음,NL 조인이 이너테이블을 여러번 중복으로 탐색하는것과는 다름.
  • ALTER SESSION SET HASH_AREA_SIZE= 104857600; -* 세션에 해쉬 사이즈 증가로 속도 개선(배치,ETL처리시)


1.2.60 USE_MERGE

/*+ USE_MERGE (table [table]...) +/ /*+ NO_USE_MERGE */

  • Sort-Merge 방식으로 각 테이블을 조인하게 합니다.
  • 참조테이블이 아우터/드라이빙 테이블 이면 적용되지 않음
  • LEADING and ORDERED 와 함께 사용할것을 권장함
  • 메모리 필요,SORT_AREA_SIZE 비용이 필요
  • 양쪽 테이블 이 모두 sort 한후 merge,별도로 SORT 할필요 없음
  • 넓은범위처리
  • 환경설정
       ALTER SESSION SET SORT_AREA_SIZE= 104857600;
   ALTER SESSION SET SORT_AREA_RETAINED_SIZE= 104857600; (같이 준다)
   ALTER SESSION SET SORT_MULTIBLOCK_READ_COUNT=128;
  • 정렬 메모리의 크기
       (= Target rows×(total selected column’s bytes) ×2) 이상
       PGA Memory Allocation Error가 발생하지 않는 범위


1.2.61 USE_NL

/*+ USE_NL (table [table]...) +/ /*+ NO_USE_NL */

  • Nested-Loop 방식으로 각 테이블을 조인.
  • LEADING and ORDERED 와 함께 사용할것을 권장함
  • 참조테이블이 OUTER 테이블이면 힌트는 무시 됨.
  • 드라이빙 테이블이 중요함
  • CPU 사용하지 않음
  • join시 이너테이블로 강제로 지정해버림(중첩되도록)
  • 드라이빙 테이블은 조회조건으로 집합구성(WHERE절의 관련 컬럼의 인덱스가 중요)
  • 드라이빙 집합으로 후행테이블이 조인키+조회조건 인덱스를 탄다
  • 두테이블에 적용되는 인덱스에 따라 자동 정렬