[Oracle] hint

oracle hint?
◎ 오라클 힌트란 ?
힌트는 SQL 튜닝의 핵심 부분으로 일종의 지시구문이며 SQL에 포함되어 쓰여져 Optimizer의 실행계획을 원하는 대로
바꿀수 있게 해줍니다. 오라클 Optimizer 라고 해서 항상 최선의 실행계획을 수립할 수는 없으므로 테이블이나 인덱스의
잘못된 실행 계획을 개발자가 직접 바꿀 수 있도록 도와주는 것이 HINT 입니다.
사용자는 특정 SQL 문장에서 어떤 인덱스가 선택도가 높은지에 대해 알고 있는데 이 것이 훨씬 효율적인 실행계획을
구사 할 수 있다는 것입니다.
힌트를 사용하게 되면 엑세스 경로, 조인의 순서, Optimizer의 목표를 변경 가능하며 SQL 문장내에 /*+ 힌트내용*/
의 형태로 사용되어 지며 여기서 주석표시와 다른점은 (+) 가 더 있다는 것이니 주의해야겠습니다.
◎ 힌트의 사용
1. 힌트구문은 기본적으로 대소문자를 구별하지 않으며 SQL 블록에서 두 가지 형태가 가능합니다.
형식1) /*+HINT*/
형식2) --+HINT (--+ 다음에 공백이 있으면 안됩니다.)
--+ 의 형식은 일부 버전의 DB에서는 인식이 안될수도 있습니다.
2. 힌트를 포함하는 주석은 SELECT, UPDATE, DELETE, INSERT 키워드 다음에만 사용할 수 있습니다.
3. 주석에 힌트가 여러개 있으면 각 힌트는 공백으로 구분되어야 합니다.
SELECT /*+FULL(E) INDEX(D pk_dept)*/ E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
4. FROM 절 다음에 테이블 ALIAS 를 사용한 경우에 힌트문에 테이블 명이 와야 한다면 반드시 ALIAS명을 기술합니다.
5. 힌트 구문이 잘못 사용되었더라도 SQL 문장이 올바르면 실행됩니다.
6. 구문 오류가 있는 힌트 문장을 무시하지만 같이 쓰여진 것 중에 올바른 힌트가 있다면 이는 인정을 합니다.
아래에서 FULL 이 FULLL로 잘못 쓰여졌지만 INDEX(D pk_dept) 힌트는 인정됩니다.
SELECT /*+FULLL(E) INDEX(D pk_dept)*/ E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
7. 만약 힌트구문이 여러개 쓰였는데 이들끼리 콤마(,)로 구분되었다면 /* 부터 , 사이의 힌트구문까지만 유효합니다.
SELECT /*+FULL(E), INDEX(D pk_dept)*/ E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
위 쿼리는 FULL(E) 까지만 유효합니다. 한 힌트내에서 인자구분으로 쓰인 , 는 유효합니다.
INDEX(E idx_ename) == INDEX(E, idx_ename)
8. 일부 데이터베이스 개발 툴에서 힌트 구문이 먹히지 않는 경우가 있는데 이는 힌트를 주석으로 간주해버려서 입니다.
이 경우 힌트가 있는 문장을 뷰로 만들어 이용하면 되는데 아래를 참고하세요.
SELECT /*+INDEX(E idx_ename)*/ *
FROM EMP E
WHERE ENAME = '홍길동'
뷰를 만듭니다.
CREATE OR REPLACE VIEW V_EMP AS
SELECT /*+INDEX(E idx_ename)*/ *
FROM EMP E;
뷰를 질의합니다.
SELECT * FROM V_EMP WHERE ENAME = '홍길동'
◎ 튜닝의 이해 참조
http://blog.naver.com/ksg011win?Redirect=Log&logNo=80014242777
# 오라클 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
힌트의 접근방법은 여러가지가 있지만, 여기선 몇가지 예를 들어본다면...
FULL(table_name) - 테이블 FULL SCAN
INDEX(table_name index_name) - INDEX SCAN (기본: 오름차순)
INDEX_ASC(table_name index_name) - 오름차순 INDEX SCAN.
INDEX_DESC(table_name index_name) - 내림차순 INDEX SCAN
그리고 이러한 힌트를 조합하여 쿼리를 만든다.
SELECT /*+ FULL (테이블명) */ column_name FROM 테이블명 ;
SELECT /*+ INDEX (테이블명 인덱스명) */ column_name FROM 테이블명 ;
SELECT --+ INDEX_DESC (테이블명 인덱스명) column_name FROM 테이블명 ;
[출처] Oracle Hint|작성자 도토리

등록일시
2013.05.24 11:32 (업로드 2013.05.24 11:32)
공개권한
비공개
태그
#Orac