"힌트 종류"의 두 판 사이의 차이
DB CAFE
(→NO_UNNEST) |
(→NO_MERGE) |
||
351번째 줄: | 351번째 줄: | ||
/*+ NO_MERGE (table) +/ | /*+ NO_MERGE (table) +/ | ||
− | * | + | * 병합 처리 방식의 사용을 방지합니다. |
− | # NO_MERGE 힌트는 외부 쿼리와 인라인 뷰 쿼리를 단일 쿼리로 결합하지 않도록 | + | # NO_MERGE 힌트는 외부 쿼리와 인라인 뷰 쿼리를 단일 쿼리로 결합하지 않도록 옵티마이저에 지시합니다. |
# 이 힌트를 사용하면 뷰에 액세스하는 방식에 더 많은 영향을 미칠 수 있습니다. | # 이 힌트를 사용하면 뷰에 액세스하는 방식에 더 많은 영향을 미칠 수 있습니다. | ||
# 예를 들어 다음 문은 seattle_dept보기가 병합되지 않도록합니다. : | # 예를 들어 다음 문은 seattle_dept보기가 병합되지 않도록합니다. : |
2021년 9월 9일 (목) 23:09 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 Oracle 힌트
- 1.1 힌트의 종류 별 분류
- 1.2 힌트의 설명 및 사용법
- 1.2.1 ALL_ROWS
- 1.2.2 AND_EQUAL
- 1.2.3 APPEND HINT
- 1.2.4 CACHE HINT
- 1.2.5 CHOOSE HINT
- 1.2.6 CLUSTER HINT
- 1.2.7 CURSOR_SHARING_EXACT
- 1.2.8 DRIVING_SITE
- 1.2.9 DYNAMIC_SAMPLING
- 1.2.10 EXPAND_GSET_TO_UNION
- 1.2.11 FACT HINT
- 1.2.12 FIRST_ROWS
- 1.2.13 FULL HINT
- 1.2.14 HASH HINT
- 1.2.15 HASH_AJ
- 1.2.16 INDEX
- 1.2.17 INDEX_ASC
- 1.2.18 INDEX_COMBINE
- 1.2.19 INDEX_DESC
- 1.2.20 INDEX_FFS
- 1.2.21 INDEX_JOIN
- 1.2.22 INDEX_SS
- 1.2.23 INDEX_SS_ASC
- 1.2.24 INDEX_SS_DESC
- 1.2.25 LEADING_HINT
- 1.2.26 MERGE
- 1.2.27 MERGE_AJ
- 1.2.28 MERGE_SJ
- 1.2.29 NL_AJ
- 1.2.30 NL_SJ
- 1.2.31 NOAPPEND
- 1.2.32 NOCACHE
- 1.2.33 NO_EXPAND
- 1.2.34 NO_FACT
- 1.2.35 NO_INDEX
- 1.2.36 NO_MERGE
- 1.2.37 NOPARALLEL
- 1.2.38 NOPARALLEL_INDEX
- 1.2.39 NO_PUSH_PRED
- 1.2.40 NO_PUSH_SUBQ
- 1.2.41 NO_QUERY_TRANSFORMATION
- 1.2.42 NO_REWRITE
- 1.2.43 NO_UNNEST
- 1.2.44 ORDERED
- 1.2.45 ORDERED_PREDICATE
- 1.2.46 OPT_PARAM
- 1.2.47 PARALLEL
- 1.2.48 PARALLEL_INDEX
- 1.2.49 PQ_DISTRIBUTE
- 1.2.50 PUSH_PRED
- 1.2.51 PUSH_SUBQ
- 1.2.52 REWRITE
- 1.2.53 ROW_ID
- 1.2.54 RULE
- 1.2.55 STAR
- 1.2.56 STAR_TRANSFORMATION
- 1.2.57 UNNEST
- 1.2.58 USE_CONCAT
- 1.2.59 USE_HASH
- 1.2.60 USE_MERGE
- 1.2.61 USE_NL
1 Oracle 힌트[편집]
1.1 힌트의 종류 별 분류[편집]
1.1.1 Optimization Goals and Approaches[편집]
- ALL_ROWS 혹은 FIRST_ROWS
- CHOOSE
- 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 */
- 최소한의 자원을 사용하여 결과값의 전체를 추출하게 합니다.
- ALL_ROWS 힌트는 최소 총 리소스 소비 인 최상의 처리량을 목표로 문 블록을 최적화하도록 최적화 프로그램에 지시합니다.
- 예를 들어 최적화 프로그램은 쿼리 최적화 접근 방식을 사용하여 최상의 처리량을 위해이 문을 최적화합니다.
- SQL 문에 ALL_ROWS 또는 FIRST_ROWS 힌트를 지정하고 데이터 딕셔너리에 액세스 한 테이블에 대한 통계가없는 경우 옵티마이저는 이러한 테이블에 할당 된 스토리지와 같은 기본 통계 값을 사용하여 누락된 통계 및 이후에 실행 계획을 선택합니다.
- 이러한 추정치는 DBMS_STATS 패키지에서 수집 한 것만 큼 정확하지 않을 수 있으므로 DBMS_STATS 패키지를 사용하여 통계를 수집해야합니다.
- ALL_ROWS 또는 FIRST_ROWS 힌트와 함께 액세스 경로 또는 조인 작업에 대한 힌트를 지정하면 최적화 프로그램이 힌트에 지정된 액세스 경로 및 조인 작업에 우선 순위를 부여합니다.
1.2.2 AND_EQUAL[편집]
/*+ AND_EQUAL (table index index [index] [index] [index] ) */
- 복수의 단일 컬럼을 스캔하여 머지 방식으로 처리하게 합니다.
1.2.3 APPEND HINT[편집]
/*+ APPEND */
- 시리얼모드 데이터베이스에서 Direct INSERT를 실행하게 합니다.
- Enterprise Edition 이 아닌 데이터베이스의 기본 모드는 시리얼 모드입니다. 이러한 직렬 모드 데이터 베이스에서의 INSERT 작업은 Conventional를 기본값으로 하고 병렬 처리 시에는 Direct INSERT를 기본값으로 합니다.
- APPEND 힌트는 최적화 프로그램이 직접 경로 INSERT를 사용하도록 지시합니다.
- 일반 INSERT는 시리얼 모드의 기본값입니다. 시리얼 모드에서는 APPEND 힌트를 포함하는 경우에만 다이렉트 패스를 사용할 수 있습니다.
- 패러럴모드에서 다이렉트 패스INSERT는 기본값입니다. 병렬 모드에서는 NOAPPEND 힌트를 지정한 경우에만 일반 INSERT을 사용할 수 있습니다.
- INSERT가 병렬로 진행 될지 여부는 APPEND 힌트와 무관합니다.
- 다이렉트 패스INSERT에서 데이터는 현재 테이블에 할당 된 기존 공간을 사용하지 않고 테이블 끝에 추가됩니다. 결과적으로 다이렉트 패스 INSERT는 기존 INSERT보다 훨씬 빠를 수 있습니다.
1.2.4 CACHE HINT[편집]
/*+ CACHE (table) +/
- FULL 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 MRU 쪽에 위치시킵니다. 작은 테이블의 사용 시 유용합니다.
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
FROM employees hr_emp;
- CACHE 및 NOCACHE 힌트는 V$SYSSTAT 데이터 딕셔너리 뷰에 표시된대로 시스템 통계 테이블 스캔 (롱 테이블) 및 테이블 스캔 (숏 테이블)에 영향을줍니다.
1.2.5 CHOOSE HINT[편집]
/*+ CHOOSE +/
- Rule-Based 와 Cost-Based 방식 간의 선택을 유도합니다. 선택 기준은 사용 객체의 분석 정보 존재 여부이며, 사용되는 객체들중 하나라도 분석 정보가 존재한다면 Cost-Based 방식을 사용하게 됩니다.
1.2.7 CURSOR_SHARING_EXACT[편집]
/*+ CURSOR_SHARING_EXACT +/
- 바인드 변수 값의 교체를 불가능하게 합니다.
- 기본적으로 CURSOR_SHARING 파라미터를 사용하여, 안전하다고 판단될 시 SQL 내의 바인드 변수 값을 교체할 수 있게 되어 있습니다.
- Oracle은 안전한 경우 SQL 문의 리터럴을 바인드 변수로 바꿀 수 있습니다.
- 이 교체는 CURSOR_SHARING 초기화 매개 변수로 제어됩니다.
- CURSOR_SHARING_EXACT 힌트는 최적화 프로그램에이 동작을 해제하도록 지시합니다.
- 이 힌트를 지정하면 Oracle은 리터럴을 바인드 변수로 바꾸지 않고 SQL 문을 실행합니다.
1.2.8 DRIVING_SITE[편집]
/*+ 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.12 FIRST_ROWS[편집]
/*+ FIRST_ROWS (n) +/
- FIRST_ROWS 힌트는 Oracle이 빠른 응답을 위해 개별 SQL 문을 최적화하도록 지시하여 처음 n 개 행을 가장 효율적으로 반환하는 계획을 선택합니다.
- 정수의 경우 반환 할 행 수를 지정합니다.
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;
이 예에서 각 부서에는 많은 직원이 있습니다. 사용자는 부서 20의 처음 10 명의 직원이 가능한 빨리 표시되기를 원합니다.
- 옵티마이져는 DELETE 및 UPDATE 문 블록과 정렬 또는 그룹화와 같은 블록킹 명령을 포함하는 SELECT문 블록에서 이 힌트를 무시합니다.
- Oracle Database는 첫 번째 행을 반환하기 전에 명령문에서 액세스 한 모든 행을 검색해야하므로 이러한 명령문은 최상의 응답 시간을 위해 최적화 할 수 없습니다.
- 이러한 명령문에이 힌트를 지정하면 데이터베이스가 최상의 처리량을 위해 최적화됩니다.
1.2.15 HASH_AJ[편집]
/*+ HASH_AJ */
- EXISTS나 IN조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_SJ 힌트를 부여하면 HASH JOIN SEMI로 처리하도록 제어
- HASH_SJ 은 hash semi-join 이고, MERGE_SJ 은 sort merge semi-join 이며 NL_SJ 은 nested loop semi-join 입니다.
1.2.16 INDEX[편집]
/*+ INDEX (table index [index] [index] ... ) */
- 지정한 테이블의 인덱스 스캔을 실행하도록 유도합니다.
- Domain, B-tree, bitmap, bitmap join 인덱스 등이 사용될 수 있으나, bitmap 인덱스 들의 사용 시, INDEX 힌트보다는 INDEX_COMBINE 힌트 사용이 추천됩니다.
- INDEX 힌트는 지정된 테이블에 대해 인덱스 스캔을 사용하도록 최적화 프로그램에 지시합니다.
- 함수 기반, 도메인, B- 트리, 비트 맵 및 비트 맵 조인 인덱스에 대해 INDEX 힌트를 사용할 수 있습니다.
- 힌트의 동작은 indexspec 사양에 따라 다릅니다.
- INDEX 힌트가 사용 가능한 단일 인덱스를 지정하는 경우 데이터베이스는 인덱스에 대해 스캔을 수행합니다.
- 옵티마이저는 FULL 테이블 스캔이나 테이블의 다른 인덱스 스캔을 고려하지 않습니다.
- 여러 인덱스 조합에 대한 힌트의 경우 Oracle은 INDEX가 아닌 INDEX_COMBINE을 사용할 것을 권장합니다. 이는보다 다양한 힌트이기 때문입니다.
- INDEX 힌트가 사용 가능한 인덱스 목록을 지정하는 경우 옵티마이 저는 목록의 각 인덱스에 대한 스캔 비용을 고려한 다음 가장 낮은 비용으로 인덱스 스캔을 수행합니다.
- 데이터베이스는 이러한 액세스 경로의 비용이 가장 낮은 경우이 목록에서 여러 인덱스를 스캔하고 결과를 병합하도록 선택할 수도 있습니다.
- 데이터베이스는 FULL 테이블 스캔 또는 힌트에 나열되지 않은 인덱스에 대한 스캔을 고려하지 않습니다.
- INDEX 힌트가 인덱스를 지정하지 않으면 옵티마이 저는 테이블에서 사용 가능한 각 인덱스에 대한 스캔 비용을 고려한 다음 가장 낮은 비용으로 인덱스 스캔을 수행합니다.
- 데이터베이스는 이러한 액세스 경로의 비용이 가장 낮은 경우 여러 인덱스를 스캔하고 결과를 병합하도록 선택할 수 있습니다.
- 옵티마이저는 FULL 테이블 스캔을 고려하지 않습니다.
1.2.17 INDEX_ASC[편집]
/*+ INDEX_ASC (table [index] [index] ... ) +/
- 해당 테이블의 인덱스를 순차적 방식으로 스캔하게 합니다.
- 해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 값의 순차적 방식으로 읽게 됩니다.
- INDEX_ASC 힌트는 지정된 테이블에 대해 인덱스 스캔을 사용하도록 최적화 프로그램에 지시합니다.
- 명령문이 인덱스 범위 스캔을 사용하는 경우 Oracle 데이터베이스는 인덱스 된 값의 오름차순으로 인덱스 항목을 스캔합니다.
- 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다.
- 범위 스캔의 기본 동작은 색인화 된 값의 오름차순 또는 내림차순 색인의 경우 내림차순으로 색인 항목을 스캔하는 것입니다.
- 이 힌트는 인덱스의 기본 순서를 변경하지 않으므로 INDEX 힌트 이상을 지정하지 않습니다.
- 그러나 기본 동작이 변경되면 INDEX_ASC 힌트를 사용하여 오름차순 범위 스캔을 명시 적으로 지정할 수 있습니다.
1.2.18 INDEX_COMBINE[편집]
/*+ INDEX_COMBINE (table [index] [index] ... ) +/
- 해당 테이블에 Bitmap 인덱스의 존재 시, Bitmap 인덱스를 통한 액세스를 유도합니다.
- 힌트 내에 인덱스의 이름이 쓰여지지 않을 시, 해당 인덱스의 Boolean 값을 사용하여 최적의 Cost를 산출하여 실행하게 됩니다.
- INDEX_COMBINE 힌트는 옵티마이저가 테이블에 비트 맵 액세스 경로를 사용하도록 지시합니다.
- INDEX_COMBINE 힌트에서 indexspec을 생략하면 옵티마이저는 테이블에 대한 최적의 비용 추정치가있는 인덱스의 Boolean 콤비네이션을 사용합니다.
- indexspec을 지정하면 옵티마이저는 지정된 인덱스의 일부 Boolean 콤비네이션을 사용하려고합니다.
- 각 매개 변수는 "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 (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 힌트는 옵티마이저에게 인덱스 조인을 액세스 경로로 사용하도록 지시합니다.
- 힌트가 긍정적인 효과를 내려면 쿼리를 해결하는 데 필요한 모든 열을 포함하는 충분히 적은 수의 인덱스가 있어야합니다.
- 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다.
- 예를 들어 다음 쿼리는 인덱스 조인을 사용하여 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 힌트는 지정된 테이블에 대해 인덱스 건너 뛰기 스캔을 수행하도록 최적화 프로그램에 지시합니다.
- 명령문이 인덱스 range scan을 사용하는 경우 Oracle은 인덱스 값의 오름차순으로 인덱스 항목을 스캔합니다.
- 파티션된 인덱스에서 결과는 각 파티션 내에서 오름차순으로 표시됩니다.
SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name
FROM employees e
WHERE first_name = 'Steven';
1.2.24 INDEX_SS_DESC[편집]
1.2.25 LEADING_HINT[편집]
/*+ LEADING (table) +/
- 테이블 간의 조인 시에 지정한 테이블을 먼저 수행하도록 유도합니다.
- 두 개 이상의 LEADING 힌트의 사용 시, 힌트 자체가 사용되어 지지 않습니다.
- ORDERED 힌트와 더불어 사용시, LEADING 힌트는 사용되지 않습니다.
- LEADING 힌트는 지정된 테이블이 조인 그래프의 종속성으로 인해 지정된 순서대로 먼저 조인 될 수없는 경우 무시됩니다.
- 두 개 이상의 충돌하는 LEADING 힌트를 지정하면 모두 무시됩니다.
- ORDERED 힌트를 지정하면 모든 LEADING 힌트를 재정의합니다.
1.2.26 MERGE[편집]
/*+ MERGE (table) +/
- 뷰/인라인뷰를 해체 하여 메인 쿼리랑 합쳐라. NO_MERGE는 뷰/인라인뷰를 해체하지 말고 합치지 말아라.
- 각 쿼리의 결과값을 머지합니다.
- 해당 쿼리 내에 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;
- 뷰의 쿼리 블록이 SELECT 목록에 GROUP BY 절 또는 DISTINCT 연산자를 포함하는 경우 옵티마이 저는 복합 뷰 병합이 활성화 된 경우에만 뷰를 액세스 문에 병합 할 수 있습니다.
- 복합 병합은 하위 쿼리가 상관되지 않은 경우 IN 하위 쿼리를 액세스 문에 병합하는 데 사용할 수도 있습니다.
1.2.27 MERGE_AJ[편집]
HASH_AJ 를 참조하십시요.
1.2.28 MERGE_SJ[편집]
HASH_AJ 를 참조하십시요.
1.2.29 NL_AJ[편집]
EXISTS나 IN조건 사용시 서브쿼리에 UNNEST(중첩해제) 와 함께 NL_SJ힌트를 사용하면, NESTED LOOPS JOIN SEMI로 처리되도록 유도
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.36 NO_MERGE[편집]
/*+ NO_MERGE (table) +/
- 병합 처리 방식의 사용을 방지합니다.
- NO_MERGE 힌트는 외부 쿼리와 인라인 뷰 쿼리를 단일 쿼리로 결합하지 않도록 옵티마이저에 지시합니다.
- 이 힌트를 사용하면 뷰에 액세스하는 방식에 더 많은 영향을 미칠 수 있습니다.
- 예를 들어 다음 문은 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;
- 뷰 쿼리 블록에서 NO_MERGE 힌트를 사용하는 경우 인수없이 지정하십시오. 주변 쿼리에 NO_MERGE를 지정하는 경우 뷰 이름을 인수로 사용하여 지정합니다.
1.2.37 NOPARALLEL[편집]
/*+ NOPARALLEL (table) +/
- 지정한 테이블의 병렬 처리를 방지합니다.
- 테이블의 지정된 PARALLEL 값에 대해서 우선권을 가집니다.
- 중첩 테이블에 대해서는 병렬 처리를 할 수 없습니다.
1.2.38 NOPARALLEL_INDEX[편집]
/*+ NOPARALLEL_INDEX (table [index] [index] ... ) +/
- 인덱스 스캔 작업의 병렬 처리를 방지합니다.
- 인덱스에 지정된 PARALLEL 값에 우선권을 가집니다.
1.2.40 NO_PUSH_SUBQ[편집]
/*+ NO_PUSH_SUBQ +/
- 서브 쿼리의 결과값을 머지하지 않는 실행 계획이 실행 계획 설립 단계에서 제일 마지막으로 참조되는 것을 방지합니다.
- 일반적으로 서브 쿼리의 Cost 가 높거나, 처리 로우의 갯수를 크게 줄여주지 못할 때에는 서브 쿼리를 마지막에 참조하는 것이 성능 향상에 도움이 됩니다.
1.2.41 NO_QUERY_TRANSFORMATION[편집]
- 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)
- NO_REWRITE 힌트는 QUERY_REWRITE_ENABLED 매개 변수의 설정을 재정 의하여 쿼리 블록에 대한 쿼리 다시 쓰기를 비활성화하도록 최적화 프로그램에 지시합니다.
1.2.43 NO_UNNEST[편집]
/*+ NO_UNNEST +/
- 뷰/서브쿼리를 해체하지 말아라. Filter 동작 방식으로 수행하도록 제어한다.
- PUSH_SUBQ : 서브 쿼리 부터 수행 해라
- NO_PUSH_SUBQ : 메인 쿼리 부터 수행해라.
1.2.44 ORDERED[편집]
/*+ ORDERED +/
- FROM 절에 나열된 테이블의 순서대로 조인 작업을 실행.
- ORDERED 힌트는 Oracle이 FROM 절에 나타나는 순서대로 테이블을 조인하도록 지시. Oracle은 ORDERED 힌트보다 다재다능한 LEADING 힌트를 사용할 것을 권장합니다.
- 조인이 필요한 SQL 문에서 ORDERED 힌트를 생략하면 옵티마이 저는 테이블을 조인 할 순서를 선택합니다.
- 옵티마이저가 각 테이블에서 선택한 행 수에 대해 알지 못하는 것을 알고있는 경우 ORDERED 힌트를 사용하여 조인 순서를 지정할 수 있습니다. 이러한 정보를 사용하면 최적화 프로그램보다 내부 및 외부 테이블을 더 잘 선택할 수 있습니다.
- 서브쿼리가 존재한다면 서브쿼리가 가장 먼저 수행됨.
1.2.45 ORDERED_PREDICATE[편집]
/*+ ORDERED_PREDICATE +/
- 옵티마이저에 의한 조인 관계의 Cost를 산출하기 위해 미리 정해둔 조인 관계 별 실행 순서의 사용을 방지합니다.
n 인덱스 키를 사용한 조인 관계들은 제외됩니다.
- 이 힌트는 쿼리의 WHERE 절에 사용하십시요.
1.2.46 OPT_PARAM[편집]
- OPT_PARAM 힌트를 사용하면 현재 쿼리 기간 동안 만 초기화 매개 변수를 설정할 수 있습니다.
- 이 힌트는 OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING 및 STAR_TRANSFORMATION_ENABLED 매개 변수에만 유효합니다.
- 예를 들어, 다음 힌트는 매개 변수가 추가되는 명령문에 대해 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.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[편집]
- 뷰/서브쿼리를 해체한다. NO_UNNEST는 반대로 뷰/서브쿼리를 해체하지 말아라.
- 서브쿼리를 메인쿼리로 몰아 내서 조인토록 유도하는 하는 힌트, 즉 중첩(NEST)을 해제 힌트 임.
- FILTER 동작방식을 선택하지 않고 조인 동작방식으로 처리하고자 할 때. 서브쿼리에 UNNEST 힌트 사용
- 하위쿼리(Sub Query)는 상위 쿼리문의 WHERE 절에 나타날 때 중첩 됨.
- Oracle Database가 중첩된 하위 쿼리가있는 문을 측정(평가)할 때 하위 쿼리 부분을 여러 번 평가해야하며 일부 효율적인 액세스 경로 또는 조인을 간과 할 수 있습니다.
- 서브쿼리 중첩해제(SubQuery UNNEST)는 서브쿼리의 본문을 포함하는 명령문의 본문에 중첩해제(Unnest) 와 병합(Merge) 하여 옵티마이저가 액세스 경로 및 조인을 평가할 때 함께 고려할 수 있도록합니다.
1.2.57.1 옵티마이저가 중첩해제를 못하는 경우[편집]
- ROWNUM 가상컬럼
- 집합 연산자 중 하나
- 중첩집계함수,서브쿼리의 외부(바깥) 쿼리 블록이 아닌 쿼리블록에 상호연관함수가 포함 된 계층적 서브쿼리 및 서브쿼리가 포함됩니다.
1.2.57.2 옵티마이저가 자동 중첩해제(UNNEST) 하는 경우[편집]
- 연관관계가 없는 IN 절의 하위 쿼리
- IN 과 EXISTS 의 연관된 서브쿼리, 집계 함수 또는 GROUP BY 절을 포함하지 않는 경우
- 옵티마이저에 추가서브쿼리 유형의 중첩을 해제하도록 지시하여 확장 서브쿼리 중첩해제를 활성화 할 수 있습니다.
- 서브쿼리에 HASH_AJ 또는 MERGE_AJ 힌트를 지정하여 상관되지 않은 NOT IN 서브쿼리의 중첩을 해제 할 수 있음.
- 서브쿼리에 UNNEST 힌트를 지정하여 다른 서브쿼리를 중첩 해제 할 수 있습니다.
- 반대로 NO_UNNEST 힌트는 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립되도록 하는 힌트임.
- WHERE절에 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 하며 IN, EXISTS 관계없이 메인쿼리에서 읽히는 FILTER방식으로 처리되어 메인레코드 하나 읽을 때마다 서브쿼리를 반복적으로 수행하면서 조건에 맞는 데이터를 추출하는 것이다.
- 이러한 필터방식이 최적의 성능을 보장하지 않으므로 옵티마이저는 조인문으로 변경후 최적화(Unnesting) 하거나 메인과 서브쿼리를 별도의 SUB PLAN으로 분리하여 각각 최적화를 수행하는데 이때 서브쿼리에 FILTER 연산이 나타난다.
- 서브 쿼리를 Unnesting 하지 않는다면 메인쿼리의 건 마다 서브쿼리를 반복 수행하는 FILTER 연산자를 사용하기에 Unnesting 힌트는 효율적으로 사용한다면 성능 향상을 가져온다.
/*+ UNNEST +/
- 서브 쿼리에 기술
- 서브 쿼리 블럭에 대해 인증성 만을 검사하게 합니다.
- 인증이 되었다면 그 이상의 검증 작업없이 서브쿼리에 대한 UNNESTING 의 설정을 가능하게 합니다.
select *
from emp
where deptno in (
select /*+ unnest */ deptno
from dept
);
/*+ NO_UNNEST +/
- 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립
1.2.57.3 같이 써줘야하는 힌트[편집]
- SWAP_JOIN_INPUTS : 해쉬테이블로 올릴 테이블 지정
- NO_SWAP_JOIN_INPUTS : 해쉬테이블로 올리지 않을 테이블 지정
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절의 관련 컬럼의 인덱스가 중요)
- 드라이빙 집합으로 후행테이블이 조인키+조회조건 인덱스를 탄다
- 두테이블에 적용되는 인덱스에 따라 자동 정렬