Database - Oracle Hint

역시 마찬가지 교육 자료.

# 오라클 Hint 구문의 사용

힌트 구문은 SQL문장(가령 SELECT, INSERT, DELETE, UPDATE)의 첫단어 이후에 /*+ hint */와 같이 나타낸다. 주석과 다른점은 /* 뒤에 스페이스 없이 '+' 기호가 있다는 점이다.

select /*+ rule*/ * from emp where sal > 3000

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'EMP'

주의할점 )
1. Hint는 대소문자를 구분하지 않는다. 아래의 구문은 모두 유효하다.

Select /*+ FULL(dept) */ * from DEPT where deptno > 10 ;
Select /*+ full(dept) */ * from DEPT where deptno > 10 ;
Select /*+ FULL(DEPT) */ * from dept where deptno > 10 ;
Select /*+ Full(Dept) */ * from DEPT where deptno > 10 ;


2. Table Alias를 사용했을 경우 힌트 구문이 테이블 명을 인수로 취해야 한다면, 반드시 인수부분에 알리어스 명을 적어주어야 한다.
Select /*+ full(Dept)*/*  from dept d where deptno > 10               ................. X

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=4 Bytes=120)
   2    1     INDEX (RANGE SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=2 Card=1)

3. 여러개의 힌트가 한 SQL 문장에 동시에 쓰일수 잇으며 이때 공백문자로 구분해 주도록 한다. (','가 아니다. ',' 이후의 힌트 문장은 무시된다.)

Select /*+ full(d) full(e)*/ * from dept d, emp e where e.deptno = d.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9K)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9K)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2K)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)

힌트 구문간이 아니라 인자를 취하는 힌트구문에서 인자의 구분값으로 ,를 사용할 수는 있으나 가능한 사용하지 말기를 권한다.
select /*+ index(e pk_emp) */ * from emp e
select /*+ index(e, pk_emp) */ * from emp e

위 두문장 모두 유효하다.

4. Schema가 명시되었거나 링크에서의 힌트는 Alias를 사용한다.

Select /*+ full(e) */ from scott.emp e where empno = 7839 ;
Select /*+ full(e) */ from emp@remote e where empno = 7839 ;

# Optimizer

 - rule, first rows, all rows, choose
10i 이후 부터는 의미 없다.

# Join Order
- ordered

- leading
Leading 힌트 구문은 테이블 조인시에 첫번째 테이블(드라이빙 테이블)로 사용될 테이블 명이나 알리아스를 인수로 취한다.

select /*+ leading(d) use_hash(e)*/ * from dept d, emp e where e.deptno = d.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9K)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9K)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2K)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)


# Join Operation
- use_nl(table..)
보통 use_nl 구무은 ordered 힌트구문과 보통 같이 사용된다. use_nl에 인수로 취하는 테이블 명은 from 절에서 두번째로 나오는, 즉 테이블 명(inner table)을 명시해 주어야 한다.

CASE 1) select /*+ ordered use_nl(e)*/* from dept d, emp e where d.deptno = e.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=166 Card=82 Bytes=9K)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=87)
   2    1     NESTED LOOPS (Cost=166 Card=82 Bytes=9K)
   3    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2K)
   4    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=1)

CASE 2) select /*+ ordered use_nl(d)*/* from emp e, dept d where d.deptno = e.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=82 Bytes=9K)
   1    0   NESTED LOOPS (Cost=84 Card=82 Bytes=9K)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=30)
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) (Card=1)

- use_merge(table)
성능 측면에서 보면 머지 조인에서는 from절에 나오는 테이블 순서가 NESTED LOOP만큼 중요하지 않다. 즉 SM에서는 드라이빙 테이블의 의미가 없다.

select /*+ ordered use_merge(d)*/* from emp e, dept d  where d.deptno = e.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=82 Bytes=9K)
   1    0   MERGE JOIN (Cost=8 Card=82 Bytes=9K)
   2    1     SORT (JOIN) (Cost=5 Card=82 Bytes=7K)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)
   4    1     SORT (JOIN) (Cost=4 Card=82 Bytes=2K)
   5    4       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2K)

- use_hash(table)
테이블중 하나를 기반으로 해시 테이블을 메모리에 생성한 후에, 나머지 테이블을 스캔하면서 해싱 함수를 적용하여, 메모리에 로드된 해치 테이블과 비교하면서 매칭이 되는 걸 뽑아내는 조인이다. 해시 조인이 제 성능을 내기 위해서는 사이즈가 작은 테이블이 메모리에 로드되는게 좋다. 즉 사이즈가 작은 테이블이 드라이빙 테이블이 되어야 한다.

특히 해시 테이블이 메모리내에 생성되면(그렇지 않으면 내부적으로 임시테이블을 만든다) 성능이 좋다. 그리고 두 테이블의 크기가 확연히 다를수록 성능이 좋아진다. 해시 조인은 또한 안티조인과 병렬 처리 기능과도 궁합이 잘 맞는다. 그리고 범위 조건이 아닌 동등비교 조인에 쓰인다.

select /*+ ordered use_hash(큰 테이블)*/ .. from 작은 테이블, 큰 테이블 where ...
select /*+ ordered use_hash(e) */ d.deptno, e.empno, e.job from dept d, emp e where e.deptno = d.deptno

--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=4K)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=4K)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1K)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=3K)

- hash_aj
not in 쿼리에 사용되어, 안티 조인을 해시 조인을 이용해서 풀리도록 하는 힌트 구문이다. 힌트구문의 명시는 아래와 같이 서브 쿼리에 명시해 주어야 한다. 또한 조인 조건의 컬럼에 NOT NULL 조건도 명시해 주어야 한다. NOT NULL 조건이 명시되지 않으면 HASH_AJ 힌트 구문은 무시된다.

Select * From emp Where empno is not null and ename is not null
   and (empno, ename) not in (select /*+ hash_aj*/ empno, ename from emp_sub_20 where empno is not null and ename is not null)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4 Bytes=428)
   1    0   HASH JOIN (ANTI) (Cost=5 Card=4 Bytes=428)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=4 Bytes=348)
   3    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=1 Bytes=20)

참고로 위의 코리는 다음과 같이 NOT EXIST를 이용하여 풀수도 있다.

Select * From emp e Where not exists (select /*+ hash_aj*/ 1 from emp_sub_20 where empno = e.empno and ename = e.ename)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=4 Bytes=348)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=4 Bytes=348)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP_SUB_20' (Cost=1 Card=1 Bytes=20)
   4    3       INDEX (RANGE SCAN) OF 'EMP_SUB_EMPNO' (NON-UNIQUE) (Cost=1 Card=1)

그러면 not in과 not exist 중에 각각 어느 상황에 사용하는 것이 더 효율적일까 ? 일단 inner table(서브쿼리에 존재하는 테이블)에 인덱스가 없을때는 hash_aj를 이용한 not in 구문이 효과적이다 inner table에 인덱스가 존재하지만, outer table의 로우수가 많을때는 hash_aj를 이용한 not in이 더 효과적이다.  inner table에 인덱스가 존재하고 아우터 테이블의 수가 적고, inner table의 로우수가 많을때는 not exists가 더 효과적이다. 기본적인 원칙일뿐 상황에 따라 조금 다르다.

- hash_sj
주로 서브 쿼리의 컬럼상에 인덱스가 존재하지 않는 Correlated Exists 쿼리에 사용되는 힌트 구문이다.

select count(*) from emp e where exists(select /*+ Hash_SJ */ 1 from dept d where e.deptno = d.deptno)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=26)
   2    1     HASH JOIN (SEMI) (Cost=5 Card=82 Bytes=2K)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=1K)
   4    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1K)

- merge_aj
Select * From emp Where empno is not null and ename is not null
   and (empno, ename) not in (select /*+ merge_aj*/ empno, ename from emp_sub_20 where empno is not null and ename is not null)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=4 Bytes=428)
   1    0   MERGE JOIN (ANTI) (Cost=7 Card=4 Bytes=428)
   2    1     SORT (JOIN) (Cost=4 Card=4 Bytes=348)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=4 Bytes=348)
   4    1     SORT (UNIQUE) (Cost=4 Card=1 Bytes=20)
   5    4       TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=1 Bytes=20)

- merge_sj
select count(*) from emp e where exists(select /*+ merge_sj*/ 1 from dept d where e.deptno = d.deptno)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=26)
   2    1     MERGE JOIN (SEMI) (Cost=7 Card=82 Bytes=2K)
   3    2       SORT (JOIN) (Cost=4 Card=82 Bytes=1K)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=1K)
   5    2       SORT (UNIQUE) (Cost=4 Card=82 Bytes=1K)
   6    5         TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1K)

#  병렬처리
- parallel(table [degree [instance]])
- noparallel(table)
- append
insert 문장에 대해 append 힌트 구문을 사용하게 되면 insert 되는 테이블에 존재하는 블럭들의 기존의 빈 공간들은 사용되지 않고, 데이타들이 Append 되게 된다. 모든 insert 구문에 사용할 수 있는 힌트 구문이 아니라, 아래와 같이 'insert .. select.. ' 패턴의 문장에서만 사용할 수 있는 힌트 구문이다. 이 힌트 구문이 사용되었을 때는, 새로이 입력되는 데이타들은 데이터 버퍼 캐쉬를 거치지 않고 바로 삽입되게 된다.

alter table emp nologging ;
insert /*+ append*/ into emp select * from emp_dummy ;

nologging 모드의 테이블에 append 모드 상태로 데이터를 insert 하게 되면, 그 작업은 리두 로그에 기록이 되지 않으므로 작업시간의 단축을 가져올수 있다. 사실상 insert 시 nologging의 효과를 보려면 반드시 /*+ append*/ 힌트 구문을 사용해야 한다. 하지만 복구시에 문제가 발생할 수 있는 여지가 있으므로 크리티컬한 테이블에 대해 nologging모드에서 append로 데이터를 삽입했다면 일단 logging로 전환한후 바로 데이타베이스 핫백업을 받아두는 것이 좋다.

alter table 테이블 명 logging ;

이와 비슷한 방식으로 sqlload x/s control=xx direct=true 도 해당된다.

- noappend
- parallel_index(table [,index [,degree [,instance]]])

#기타

- cache

보통 풀스캔되어 SGA의 DB의 버퍼 캐시 영역에 올라간 데이타 블럭들은 사용후에 바로 사라지게끔 알고리즘화 되어 있다. 하지만 CACHE 힌트를 쓰게되면 그 풀스캔된 데이터들을 SGA의 버퍼캐시 영역에 보다 오래토록 보존해 준다.
그러므로 이러한 힌트 구문은 테이블 사이즈가 작으면서 자주 억세스 되는 테이블에 사용하면 유용하다.

select /*+ Full(dept) cache(dept) */ * from dept where deptno > 0

그러나 실행계획만으로 Cache 여부를 알수는 없다.

- nocache

Cache의 반대 구문으로 보통의 경우 NoCache 특성은 테이블 풀스캔시의 디폴트 속성이므로 굳이 이러한 구문을 명시할 필요는 없다.

- merge(viewname)
뷰를 나머지 쿼리와 융합시켜주는 역할을 해주는 힌트 구문이다. Oracle 8i에는 버그가 있어 제대로 작동하지 않는다. (히든 파라미터인 _COMPLEX_VIEW_MERGING=TRUE로 설정되면 가능) Oracle 9i부터 COMPLEX_VIEW_MERGING=TRUE로 세팅되면 View Merge가 가능하다.

CASE 1)
select /*+ merge(v)*/ * from emp e, (select deptno, dname from dept where loc = 'CHICAGO') v
where e.deptno = v.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=117)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=87)
   2    1     NESTED LOOPS (Cost=4 Card=1 Bytes=117)
   3    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=30)
   4    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=1)

CASE 1-1)
select * from emp e, (select rownum, deptno, dname from dept where loc = 'CHICAGO') v
where e.deptno = v.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     NESTED LOOPS
   3    2       VIEW
   4    3         COUNT
   5    4           TABLE ACCESS (FULL) OF 'DEPT'
   6    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

CASE 2)
select * from emp e,  (select deptno, avg(sal) avg_sal from emp e group by deptno) v
where e.deptno = v.deptno and e.sal > v.avg_sal

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     NESTED LOOPS
   3    2       VIEW
   4    3         SORT (GROUP BY)
   5    4           TABLE ACCESS (FULL) OF 'EMP'
   6    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

CASE 3)
select /*+ merge(v)*/ * from emp e,  (select deptno, avg(sal) avg_sal from emp e group by deptno) v
where e.deptno = v.deptno and e.sal > v.avg_sal

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=82 Bytes=10K)
   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=7 Card=82 Bytes=10K)
   3    2       HASH JOIN (Cost=5 Card=82 Bytes=10K)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=2K)
   5    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=8K)

- no_merge(viewname)

CASE 1-2)
select /*+ no_merge(v)*/ * from emp e,  (select deptno, dname from dept where loc = 'CHICAGO') v
where e.deptno = v.deptno

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=109)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=87)
   2    1     NESTED LOOPS (Cost=4 Card=1 Bytes=109)
   3    2       VIEW (Cost=2 Card=1 Bytes=22)
   4    3         TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=30)
   5    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=1)

- push_pred(viewname)
ver 8i에서는 버그로 인해 제대로 작동되지 않았으며, ver 9i부터 적용 가능하다. 테이블이 뷰와 Outer Join을 할 경우에 적용할 수 잇는 힌트 구문이다. 이 경우 보통 뷰안의 쿼리가 독립적으로 실행이 된후에 조인이 일어나기 마련이지만 PUSH_PRED 힌트 구문을 사용하면 뷰안에 조인 조건이 녹아 들어가 영향을 주게 된다.

CASE 1)
Select * From emp_sub_20 s, (Select e.empno, d.dname, e.ename from emp e, dept d Where e.deptno = d.deptno) v
Where s.empno = v.empno(+)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN (OUTER)
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'EMP_SUB_20'
   4    1     SORT (JOIN)
   5    4       VIEW
   6    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   7    6           NESTED LOOPS
   8    7             TABLE ACCESS (FULL) OF 'DEPT'
   9    7             INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

CASE 2)
Select /*+ PUSH_PRED(v)*/* From emp_sub_20 s,  (Select e.empno, d.dname, e.ename from emp e, dept d Where e.deptno = d.deptno) v Where s.empno = v.empno(+)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=82 Bytes=4K)
   1    0   NESTED LOOPS (OUTER) (Cost=84 Card=82 Bytes=4K)
   2    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=82 Bytes=2K)
   3    1     VIEW PUSHED PREDICATE (Card=1 Bytes=29)
   4    3       NESTED LOOPS (Cost=2 Card=1 Bytes=55)
   5    4         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=33)
   6    5           INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=1)
   7    4         TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=22)
   8    7           INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) (Card=1)

- no_push_pred

- push_subq
보통 correlated subquery까지 포함되어 있는 테이블 조인에서는, 조인이 먼저 일어난 후에 subquery가 마지막에 실행되기 마련이다. 서브쿼리의 실행 비용이 고비용이고, 반환하는 로우도 상당하다면, 서브 쿼리를 먼저 실행시키는 것이 성능에 도움이 될 것이다.

CASE 1)
Select * from emp e, emp_sub_20 s Where e.empno = s.empno and exists(select 1 from dept d where deptno = s.deptno)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'EMP_SUB_20'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   5    4         INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
   6    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

CASE 2)
Select /*+ push_subq */* from emp e, emp_sub_20 s Where e.empno = s.empno
    and exists(select 1 from dept d where deptno = s.deptno)

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=88K Bytes=11M)
   1    0   NESTED LOOPS (Cost=84 Card=88K Bytes=11M)
   2    1     NESTED LOOPS (SEMI) (Cost=2 Card=82 Bytes=4K)
   3    2       TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=82 Bytes=3K)
   4    2       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) (Card=82 Bytes=1K)
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=88K Bytes=7M)
   6    5       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) (Card=1)

- no_push_subq
- star
- star_transformation
- driving_site