Oracle Hint의 정의와 종류

힌트는 SQL 튜닝의 핵심부분으로 일종의 지시구문이다.

SQL에 포함되어 쓰여져 Optimizer의 실행 계획을 바꿀수 있도록 말그대로 Optimizer에게 힌트를 주는 것이다.

Optiomizer라고 해서 항상 최선의 실행 계획을 수립할 수는 없으므로 테이블이나 인덱스의 잘못된 실행 계획을 사용자가 직접 바꿀 수 있도록 도와주는 지시구문.

 

오라클 힌트 종류

SELECT /*+ INDEX(idx_col1) */              id, password, name   FROM emp;

SELECT /*+ ORDERED  INDEX(b idx_col1) */              id, password, name   FROM emp a          , depart b

※ 주의! 주석 표시 뒤에 '+' 기호가 있다.
 

◆ INDEX Access Operation 관련 HINT

 

HINT	내용	사용법
INDEX	INDEX를 순차적으로 스캔	INDEX(TABLE_name  INDEX_name)
INDEX_ASC	INDEX를 내림차순으로 스캔.
INDEX_DESC	INDEX를 오름차순으로 스캔.	INDEX_DESC(TABLE_name INDEX_name)
INDEX_FFS	INDEX FAST FULL SCAN	INDEX_FFS(TABLE_name INDEX_name)
PARALLEL_INDEX	INDEX PARALLEL SCAN	PARALLEL_INDEX(TABLE_name INDEX_name)
NOPARALLEL_INDEX	INDEX PARALLEL SCAN 제한	NOPARALLEL_INDEX(TABLE_name INDEX_name)
AND_EQUALS	여러개의 INDEX MARGE 수행	AND_EQUALS(INDEX_name INDEX_name)
FULL	FULL SCAN  지정된 테이블에 대한 전체 스캔.	FULL(TABLE_name)

 

◆ JOIN Access Operator 관련 HINT

HINT	내용	사용법
USE_NL	NESTED LOOP JOIN  옵티마이저가 NESTED LOOP JOIN을 사용하도록 한다.  먼저 특정 TABLE의 ROW에 액세스하고 그 값에 해당하는 다른 TABLE의 ROW를 찾는 작업을 해당범위까지 실행하는 조인.	USE_NL(TABLE1, TABLE2)
USE_NL_WITH_INDEX	INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 한다.	USE_NL_WITH_INDEX(TABLE  INDEX)
USE_MERGE	SORT MERGE JOIN  옵티마이저가 SORT MERGE JOIN을 사용하도록 한다.  먼저 각각의 TABLE의 처리 범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOIN하는 방식.	USE_MERGE(TABLE1, TABLE2)
USE_HASH	HASH JOIN  옵티마이저가 HASH JOIN을 사용하도록 한다.	USE_HASH(TABLE1, TABLE2)
HASH_AJ	HASH ANTIJOIN	HASH_AJ(TABLE1, TABLE2)
HASH_SJ	HASH SEMIJOIN	HASH_SJ(TABLE1, TABLE2)
NL_AJ	NESTED LOOP ANTIJOIN	NL_AJ(TABLE1, TABLE2)
NL_SJ	NESTED LOOP SEMIJOIN	NL_SJ(TABLE1, TABLE2)
MERGE_AJ	SORT MERGE ANTIJOIN	MERGE_AJ(TABLE1, TABLE2)
MERGE_SJ	SORT MERGE SEMIJOIN	MERGE_SJ(TABLE1, TABLE2)

 

 

◆ JOIN시 DRIVING 순서 결정 HINT

HINT	내용	사용법
ORDERED	FROM절에 명시된 테이블의 순서대로 DRIVING
LEADING	파라미터에 명시된 테이블의 순서대로 JOIN	LEAING(TABLE_name1, TABLE_name2, ...)
DRIVING	해당 테이블을 먼저 DRIVING	DRIVING(TABLE)

 

◆ 기타 HINT

HINT	내용	사용법
APPEND	INSERT시 DIRECT LOADING
PARALLEL	SELECT, INSERT시 여러개의 프로세스로 수행	PARALLEL(TABLE, 개수)
CACHE	데이터를 메모리에 CACHING
NOCACHE	데이터를 메모리에 CACHING하지 않음
PUSH_SUBQ	SUBQUERY를 먼저 수행
REWRITE	QUERY REWRITE 수행
NOREWIRTE	QUERY REWRITE를 수행 못함
USE_CONCAT	IN절을 CONCATENATION ACCESS OPERATION으로 수행
USE_EXPAND	IN절을 CONCATENATION ACCESS OPERATION으로 수행못하게 함
MERGE	VIEW MERGING 수행
NO_MERGE	VIEW MERGING 수행못하게 함

※ 추가

ALL_ROWS : 가장 좋은 단위 처리량의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (즉, 전체적인 최소의 자원 소비, 모든 레코드의 처리하는 시간의 최소화를 목적으로 최적화)

FIRST_ROWS : 가장 좋은 응답 시간의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (첫번째 레코드의 추출 시간을 최소화할 목적으로 최적화)

CHOOSE : 최적자(optimizer)가 그 문에 의해 접근된 테이블을 위해 통계의 존재에 근거를 두는 SQL문을 위해 rule-based 접근 방법과 cost-based 접근 방법 사이에 선택하게 됩니다.

CLUSTER : 지정된 테이블에 대한 클러스터 스캔.

HASH : 지정된 테이블에 대한 해쉬 스캔.

ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔.

RULE : explicitlly chooses rule-based optimization for a statement block. rule-base Optimizer를 사용.

◆ 주의

SELECT /*+ ORDERED USE_NL(A B C) INDEX(B ITOREDRETL1) */     FROM TORDERDTL  B, TORDER  A, TITEM  C   WHERE ...

1. ORDERED : FROM 절에 기술한 테이블 순(B → A → C 순)으로 조인함. USE_NL에 사용한 순서가 아님.

    ※ 참고) LEADING, 예) LEADING(B) : FROM 절의 기술 순서와 상관없이 B테이블이 가장 먼저 선행됨.

2. USE_NL : 조인방법을 Nested Loops방식으로 선택.

    예) USE_NL(A B), 테이블명이 아닌 테이블에 대한 Alias명

    참고) USE_HASH, USE_MERGE

3. INDEX : 특정 인덱스를 오름차순으로 읽음.

    예) INDEX(B  ITORDERDTL1) : B는 TORDERDTL 테이블의 Alias명, 사용할 인덱스명 기술

    참고) USE_DESC(B ITORDERDTL1) : ITORDERDTL1 인덱스를 내림차순으로 읽음

 

◆ 실행계획 살펴보기 1

SELECT *     FROM ( ==> 인라인뷰 3                 ( ==> 인라인뷰 2                    ( ==> 인라인뷰 1                    )                 )               )

과 같이 되어 있다고 하자.

이럴 경우 실행계획을 보면, 우리가 예상했던대로 인라인뷰1 ==> 인라인뷰2 ==> 인라인뷰3 순서로 드라이빙 되지 않는 경우가 있다.

이런 경우 강제로 위 순서대로 실행계획을 수립하도록 하는 힌트

SELECT /*+ ORDERED */

            *

  FROM ( .....

위 힌트를 사용하면 맨 깊숙한 인라인뷰부터 실행된다.

 

 

◆ 힌트 사용하기

1. /*+ USE_CONCAT */

USE_CONCAT : 조건절에 있는 OR 연산자조건 (또는 IN 연산자 조건)을 별도의 실행단위로 분리하여 각각의 최적의 액세스 경로를 수립하여 이를 연결(Concatenation)하는 실행계획을 수립하도록 유도하는 힌트.

반드시 처리주관 조건이 OR로 나누어졌을 때 적용해야 하며, 잘못 사용하면 비효율이 발생할 수 있으므로 주의해야 함.

예>

SELECT /*+ USE_CONCAT */

   FROM employees

 WHERE job = &job

       OR dept_no = &deptno;

풀어쓰자면

SELECT *

    FROM employees

  WHERE job = &job

UNION ALL

 

SELECT *

    FROM employees

WHERER dept_no = &deptno;

WHERE 절 이후에 나오는  컬럼에 맞게 인덱스를 탄다.

 

2. /*+ NO_EXPAND */

조건절에 있는 OR 연산자 조건 또는 IN 연산자 조건을 연결 실행계획으로 처리되지 않도록 할 때 사용하는 힌트.

USE_CONCAT의 반대 개념.

예>

SELECT /*+ NO_EXPAND */

   FROM customer

 WHERE cust_type in ('A','B');

참고 : http://blog.naver.com/hkjhc107?Redirect=Log&logNo=130035827974

 

 

◆ 참고

1. Nested Loop

   - 테이블의 인덱스끼리 inner-outer 루프를 형성하여 결과를 쿼리하는 방식입니다.

   - 제일 많은 유형의 실행계획입니다.

2. Sort Merge

   - 쿼리의 결과가 많은 양의 데이터를 읽는 경우, 테이블들을 각각 full-scan하여 같은 키값을 갖는 데이터끼리 조인하여 실행합니다.

   - Sort-Merge 방식은 많은 메모리와 디스크 I/O를 필요로 하기 때문에, sqlplus를 실행하는 주체의 메모리/CPU/디스크 스펙에 많은 영향을 받습니다.

3. Hash Join

   - 한 테이블은 매우 많은 Row를 갖고, 다른 한 테이블은 매우 적은 Row를 가질 때, 해쉬 알고리즘에 의해 큰 테이블을 여러개의 버켓으로 나누어 쿼리를 수행하는 방식입니다. 작은 테이블은 인덱스를 태우는 것보다 full-scan을 하는 것이 유리할 때 사용됩니다.

 

 

[출처]

1. http://f1angel.tistory.com/139

2. http://spring3oyh.blogspot.kr/2011/11/blog-post_03.html