오라클 힌트 사용법

INDEX 컬럼의 결정 방법과 HINT 사용
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

인덱스로 만들 컬럼은 다음과 같은 기준에서 선택한다.

1. WHERE 절에서 function의 input이 아니면서, 빈번히 사용되는 컬럼들.
2. 선별력이 높은 컬럼들 (테이블에서 10% 미만인 데이터)
3. 크기가 적은 테이블에서는 인덱스를 만들지 않는다.

DB에서 retrieve되는 속도가 느릴 경우, 먼저 Execution Plan을 만들어 보면
어떤 인덱스를 사용하는지 알 수 있다. 만약, User가 기대한 생성한 인덱스를
사용하지 않을 경우, 다음에서 설명하고 있는 Hint 절을 추가하여 retrieval
속도를 향상시킬 수 있다.

Optimizer Hint Syntax

-----> SELECT -----> /*+ hint 절*/ ------->
DELETE
UPDATE

또는

-----> SELECT -----> --+ hint 절 ------->
DELETE
UPDATE

Hint 절에는 다음 조건들을 부여할 수 있다.

1. FULL(table_name)
: table을 full scan하길 원할 때 사용함.

2. INDEX(table_name index_name[index])
: 특정 index를 사용하도록 할 때 사용함.

3. INDEX_ASC(table_name index_name[index])

4. INDEX_DESC(table_name index_name[index])

5. AND_EQUALS(table_name index_name index_name[index])

6. ROWID(table_name)

7. CLUSTER(table_name)

8. HASH(table_name)

9. ORDERED

10. USE_MERGE(table_name)

11. USE_NL(table_name)

Hint를 사용하여 특정 index를 사용한 update의 예

(이 결과는 7.3 이상에서 제공되는 auto trace를 사용하는 방법이나
execution plan 또는 tkprof를 이용하여 확인할 수 있다.)

Update /*+ INDEX (e1 e_job) */
Emp e1
SET sal =
(SELECT --+ INDEX (e1 e_dept)
(e1.sal + AVG(e2.sal))/2
FROM emp e2
WHERE e2.deptno = e1.deptno)
WHERE job = 'CLERK'
AND deptno BETWEEN 10 AND 40;

힌트란 사용자가 액세스 경로의 변경을 위해서 SQL내에 요구사항을 기술하면 옵티마이져가
액세스 경로를 결정할때 이를 참조하도록 하는 사용자 인터페이스를 말합니다.

옵티마이저에게 모든것을 맡기지 않고 사용자가 원하는 보다 좋은 액세스 경로를 직접
선택할 수 있도록 하므로 보다 쉽게 최적의 튜닝을 할 수 있게 도와 줍니다.

실행경로를 옵티마이져가 파악하는 것이 아니라 사용자가 직접 정해주는 거라고 생각하면 쉽죠..


◈ 힌트의 사용 방법
힌트를 사용하는 방법은 '/*+ */'와 '--+'의 두가지 방법이 있습니다.

/*+ */ : 여러 라인에 걸쳐 기술 할 때 사용합니다.
--+ : 오직 한 라인에만 힌트를 기술할 수 있고 컬럼은 반드시 다음 라인에 기술해야 합니다.

SELECT /*+ INDEX(테이블명 PK명) */ 칼럼명 FROM ...
지정한 테이블의 PK가 걸려있는 컬럼의 인덱스를 사용해서 SQL문의 액세스 경로를 구하는 거고요.

SELECT /*+ INDEX_DESC(테이블명 PK명) */ 칼럼명 FROM ...
DESC는 ORDER BY를 사용하지 않고도.. SQL문의 결과값을 기본적으로 DESCending으로 정렬을 해주는 거죠.

힌트는 종류가 굉장히 많습니다. 참고해 보세요.

<<Access Methods - 접근 방법>>
/*+ CLUSTER(table_name) */
Cluster Scan을 선택하도록 지정한다. 따라서 clustered object들에만 적용 됩니다.

/*+ FULL(table_name) */
Table을 Full Scan하길 원할 때 사용 합니다.

/*+ HASH(table) */
Hash scan을 선택하도록 지정한다.
이 hint는 HASHKEYS parameter를 가지고 만들어진 cluster내에 저장된 table에만 적용이 됩니다.

/*+ INDEX(table_name index_name) */
지정된 index를 강제적으로 쓰게끔 지정 합니다.

/*+ INDEX_ASC(table_name index_name) */
지정된 index를 오름차순으로 쓰게끔 지정 합니다.
Default로 Index Scan은 오름차순 입니다

/*+ INDEX_DESC(table_name index_name) */
지정된 index를 내림차순으로 쓰게끔 지정 합니다.

SQL>SELECT /*+ index_desc(emp pk_emp) */ empno
FROM emp
WHERE rownum = 1 ;

위 문장은 제일 큰 것 하나만 조회되므로, max function의 기능을 대신할 수 있습니다.

/*+ INDEX_FFS(table index) */
Full table scan보다 빠른 Full index scan을 유도 합니다.

/*+ ROWID(table) */
Rowid로 Table Scan을 하도록 지정 합니다.


<<Optimization Approaches and Goals - Optimization 접근과 목적>>
/*+ ALL_ROWS */
ALL_ROWS는 Full Table Scan을 선호하며 CBO(Cost Based Optimization)는 default로
ALL_ROWS를 선택 합니다.

SQL>SELECT /*+ ALL_ROWS */ ename, hiredate FROM emp WHERE ename like '%%%'

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=5 Bytes=80)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=80)


/*+ CHOOSE */
Hint Level의 CHOOSE는 RBO(Rule Based Optimization)인지 CBO(Cost Based Optimization)
인지를 선택 합니다.
만약 주어진 table의 통계 정보가 없다면 Rule Based 접근 방식을 사용 합니다.

/*+ FIRST_ROWS */
Full Table Scan보다는 index scan을 선호하며
Interactive Application인 경우 best response time을 제공 합니다.
또한 sort merge join보다는 nested loop join을 선호 합니다.

SQL>SELECT /*+ FIRST_ROWS */ ename FROM emp WHERE empno=7876

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=1)

/*+ RULE */
Rule Based 접근 방식을 사용하도록 지정 합니다.