Oracle 중요 힌트 Hint

출처 : http://blog.naver.com/itough2323?Redirect=Log&logNo=80125972268

 

 

◆ 오라클 힌트 사용예

 

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_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을 사용하도록 한다.	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을 사용하도록 한다.	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 수행못하게 함

 

 

 

 

중요 Hint
1. Select --+ RULE empno, ename from emp where empno=7788
한 SQL문 블록에 대해 RBO 사용

2. Select /*+ALL_ROWS*/ empno, ename from emp where empno=7788
총자원 소비의 최소화 가장 좋은 처리 성능을 목표로 SQL문 블록을 최적화하기 위해 CBO 접근 선택

3. Select /*+FIRST_ROWS*/ empno, ename from emp where empno=7788
첫 번째 행을 찾는 최소의 자원 사용 CBO 접근

4. Select /*+CHOOSE*/ empno, ename from emp where empno=7788
엑세스되는 테이블의 통계 정보 존재 여부에 따라 옵티마이저로 하여금 RBO, CBO 중 하나를 선택

5. select /*+FULL(a)*/ empno, ename from emp a where empno=7788
해당 테이블의 Full Table Scan을 유도

6. select /*+ROWID(EMP)*/ empno, ename from emp a where rowid > 'AAAAtkAAABFV'
지정된 테이블의 Rowid를 이용한 검색을 유도

7. select /*+CLUSTER(EMP)*/ empno, deptno from emp, dept where deptno=10 and emp.deptno = dept.deptno
지정된 테이블 Access에 Cluster Scan을 유도

8. select /*+INDEX(EMP EMPNO_INDEX)*/ empno, ename from emp where deptno=10
지정된 테이블 Access에 Index Scan을 유도

9. select /*+INDEX_ASC(EMP EMPNO_INDEX)*/ empno, ename from emp where deptno<10
지정된 Index의 오름차순 Scan을 유도

10. select /*+INDEX_DESC(EMP EMPNO_INDEX)*/ empno, ename from emp where deptno>10
지정된 Index의 내림차순 Scan을 유도

11. select /*+INDEX_COMBINE(EMP SAL_BMI HIREDATE_BMI)*/ sal, hiredate from emp where sal < 50000
Index 명이 주어지지 않으면 옵디카이저는 해당 테이블의 Best Cost로 선택된 Boolean Combination Index를 사용
Index 명이 주어지면 특정 Bitmap Index의 Boolean Combination의 사용을 시도

11. select /*+INDEX_JOIN(EMP SAL_BMI HIREDATE_BMI)*/ sal, hiredate from emp where sal < 50000
인덱스 조인을 유도한다. 효과적이기 위해서는 중분히 작은 수의 인텍스들이 쿼리를 풀기 위해 필요한 모든 컬럼을 가져야 함.

12. select /*+INDEX_FFS(EMP EMP_EMPNO)*/ empno, ename from emp where empno > 10
조회하는 컬럼들이 인덱스 내에 모두 있는 경우 사용 multi block I/O를 하므로 성능이 좋음

12. select /*+NO_INDEX(EMP EMP_EMPNO)*/ empno, ename from emp where empno > 10
지정된 테이블의 인덱스 중에서 제외 시킬 Index 지정

13. /*+AND_EQUAL(table index1, index2...) */
Sing Colum Index의 Merge를 이용한 Access Path를 선택

14. select /*+USE_CONCAT*/ empno, ename from emp where empno > 10 OR
where 절의 OR 조건을 Union ALL 형식으로 변형

15. NO_EXPAND
where 절에서 OR 절이나 IN-LIST 절의 전계를 CBO 모드에서 사용하지 않게 함

16. /*+ORDERED*/
from 절에 기술된 테이블 순서대로 Join이 일어나도록 유도

17. /*+ORDERED USE_NL(table, table)*/
테이블의 Join 시 드라이빙 테이블의 각 Row가 드리븐 테이블을 NESTED LOOP 형식으로 조인

18. /*+USE_MERGE(table, table)*/
지정된 테이블의 조인이 Sort Merge 조인으로 처리되도록 유도

19. /*+USE_HASH(table, table)*/
각 테이블 간에 HASH JOIN이 일어나도록 유도

20. /*+STAR*/
STAR QUERY PLAN이 사용가능하다면 이를 이용하기 위한 HINT

21. /*+DRIVING_SITE(table)*/
쿼리의 실행이 다른 Site에서 일어나도록 유도(DBLINK 사용하여 Remote Server와 조인시)

22. /*+LEADING(table)*/ 
조인 순서에서 지정된 테이블이 Driving 테이블이 되도록 함. Ordered 힌트와 같이 사용되는 경우 Leading 힌트는 무시

23. /*+FULL(DEPT) CACHE(table)*/
전체 범위로 읽은 Table을 데이터베이스 버퍼의 LRU 리스트 앞쪽에 두어 버퍼에 오래 남아있을 확률을 높인다. 자주 액세스되는 적은 크기으 테이블에 유용

24. /*+NOCACHE(table)*/
버퍼에서 제거되는 확률을 높이게 하는 힌트로 사용 빈도가 적고 큰 크기의 테이블에 사용

[출처] Oracle 중요 Hint