Life is Simple, Thinking is High

select ...
from a, b
where a.col1 = b.col1
   and b.col2 like 'abc%'
   and a.col2 in (select col2 from c where condition)

라는 SQL이 있다고 할때 SQL의 구조를 바꾸지 않고 in절의 sub쿼리가 항상 먼저 실행되도록 만들어라. 최소 3가지 이상의 방법을 고민해 보고 장단점을 생각해보자.


특정 테이블을 명시하지 않은 것은 위의 테이블이 어떤 테이블이건 그리고 어떤 조인을 사용하는지에 상관없어야 하기 때문이다. 보통의 경우 in subquery는 조인후에 실행된다. (항상 그런건 아니고 단순히 확률을 말함) 만약 위 쿼리문이 Hash 로 풀린다면 먼저 in subquery가 실행되는것과 a, b 조인후에 in subquery가 실행되는 것은 하늘과 땅차이다. 물론 NL이나 SM도 많은 차이를 가진다. 문제는 위 쿼리가 어떤 실행계획을 사용하든지 상관없어야 하고 또 이전 조인 방법을 바꾸지 않아야 하기 때문에

select ...
from c, a, b
where ....

와 같은 식으로 바꿔서는 안된다는 점이다.

역시 마찬가지 교육 자료.

# 오라클 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

 

 

예전에 교육 목적으로 작성한 자료..

실행계획의 해석 순서

1. 실행계획상에서 가장 안쪽으로 들여쓰기된 문장이 가장 먼저 수행된다.

2. 각 문장은 들여쓰기가 한단계 덜한 부모 문장에 종속된다.

3. 안쪽으로 동일하게 들여 쓴 문장이 여럿 있을때는 가장 위쪽의 문장이 먼저 수행된다. (단 예외적으로 인덱스를 경유하여 테이블 억세스하는 두개의 실행계획 문장은 하나로 취급한다. 이 경우는 가장 안쪽으로 들여쓰기된 문장이 먼저 수행된다는 룰을 따르지 않는다.)

4. NestedLoop인 경우 NESTED LOOPS 바로 밑에 나온 문장이 드라이빙 테이블(DRIVING | OTER TABLE)에 대한 억세스를 나타내며, 그 아래 문장은 비드라이빙 테이블(PROBED | INNER TABLE) 테이블의 억세스를 의미한다.


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


위 예제의 경우 EMP의 각 로우마다 추출된 DEPTNO를 이용해서 PK_DEPT 인덱스를 억세스 한후에 인덱스에서 얻어진 ROWID를 근거로 DEPT 테이블을 억세스하여 원하는 로우를 추출하는 실행계획을 보여주고 있다.

Nested Loop는 (Filter도 동일) 아래의 문장(Table Access By Index RowId of Dept)이 위 문장의 Row수 만큼 반복이 된다. 즉 3-4번은 2번의 행수만큼 반복 실행이 되었다는 뜻이다.


Case 2)
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)

그에 반해 위 Hash Join의 실행계획은 2번과 3번은 한번씩 실행이 되었다는 뜻이다.



# TABLE

TABLE ACCESS BY ROWID
조건으로 주어진 컬럼의 인덱스에 존재하는 ROWID를 값을 근거로 테이블을 억세스하여 해당로우를 추출해내는 로우 연산. ex) select * from emp e where empno = 7369
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)


TABLE ACCESS FULL
ex)  select * from emp
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




# INDEX

INDEX UNIQUE SCAN
ex) select * from emp e where empno = 7876
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)


INDEX RANGE SCAN [DESCENDING]
where절의 인덱스 구성 컬럼이 <, >, Between, Like 혹은 복합인덱스의 선두컬럼중의 일부만 사용되었을때 나타난다.  기본적으로 index scan은 single block io를 한다.

ex) select * from emp where deptno >= 20
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

보통의 경우 인덱스 생성시 자동으로 그 컬림이 오름차순으로 정렬된 형태의 인덱스가 생성된다. 그러므로 인덱스를 경유하게 되면, 그 컬럼 값들은 자동으로 오름차순 정렬 형태로 추출되게 된다. 그러나 INDEX_FFS와 같은 힌트구문을 이용하여 INDEX FULL SCAN을 행해서 얻어진 결과 셋의 경우에는 Multi Index Block Scan을 할수 있으므로 사용 인덱스 컬럼을 기준으로 볼때 정렬되지 않은 결과를 보여줄수도 있다.

ex) select /*+ index_desc(e emp_deptno_idx)*/* from emp e where deptno >= 20
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=348)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=4 Bytes=348)
   2    1     INDEX (RANGE SCAN DESCENDING) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=2 Card=1)


INLIST ITERATOR

ex) select /*+ all_rows*/* from emp e where empno in (7876, 7900, 7902)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=1 Bytes=87)
   1    0   INLIST ITERATOR
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=87)
   3    2       INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=2 Card=1)

ex) select * from emp e where empno in (7876, 7900, 7902)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   3    2       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   5    4       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   7    6       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

AND-EQUAL

ex) select * from emp x1 where job = 'CLERK' and deptno = 20
Execution Plan
--------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     AND-EQUAL
   3    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)





# JOIN

NESTED LOOPS
조인되는 컬럼중 적어도 하나의 인덱스가 존재하며 그 인덱스를 사용할때 나타나는 로우 연산이다. 실행계획상에서 상위에 위치하는 테이블을 드라이빙 테이블이라 하고, 하위에 위치한 테이블을 비드라이빙 테이블이라고 한다.

ex) select * from emp x1, dept x2 where x1.deptno = x2.deptno
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

만약 Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=166 Card=82 Bytes=9K)
   1    0   NESTED LOOPS (Cost=166 Card=82 Bytes=9K)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=30)
위와 같은 실행계획이 보여진다면 성능측면에서 좋지 못할 것이다. 보통 이러한 실행계획은 힌트 구문을 사용하여 억지로 만들지 않는한 오라클에 의해서 만들어 지지는 않을 것이다.(두테이블이 아주 작으면 만들어질수도 있다.) 이 경우 오라클은 자동적으로 성능을 생각해서 MERGE나 HASH가 발생하는 실행계획을 세운다.

두 테이블 모두 조인컬럼에 인덱스가 있고 모두 사용 가능할 경우 Rule Base Optimizer는 뒤에 테이블을 드라이빙 테이블로 선정한다. 물론 한쪽에만 인덱스가 존재할 경우 인덱스가 존재하는 쪽이 드라이빙이 된다. Cost Base에서는 특별히 힌트를 사용하지 않는 이상 통계정보에 의해 드라이빙 테이블이 결정된다.

MERGE JOIN
ex) select *  from emp e, dept d where d.deptno +0 = e.deptno +0
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'EMP'


HASH JOIN
ex) select /*+ use_hash(x1 x2) */ * from dept x1, emp x2 where x1.deptno = x2.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)

상위에 위치한 DEPT 테이블이 메모리에 로드되게 되고, 오라클은 해싱 함수를 이용해서 EMP 테이블의 로우들을 메모리에 로드된 값들과 비교하여 원하는 값을 추출한다.

[HASH | MERGE] ANTI JOIN

anti join의 예) not in / not exists / minus

HASH ANTI-JOIN
ex) create table emp_sub_20 as select empno, ename from emp where deptno = 20 ;
create index emp_sub_empno on emp_sub_20(empno) ;

CASE 1)
select * from emp where (empno) not in (select empno from emp_sub_20)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20'

CASE 2)
select * from emp x1 where not exists(select 1 from emp_sub_20 where empno = x1.empno)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     INDEX (RANGE SCAN) OF 'EMP_SUB_EMPNO' (NON-UNIQUE)

CASE 3)
select empno, ename, job from emp x1 minus select empno, ename, job from emp_sub_20
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MINUS
   2    1     SORT (UNIQUE)
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    1     SORT (UNIQUE)
   5    4       TABLE ACCESS (FULL) OF 'EMP_SUB_20'

CASE 4)
select * from emp where  empno is not null
   and (empno) not in (select /*+ hash_aj*/ empno from emp_sub_20 where empno is not null)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=77 Bytes=8K)
   1    0   HASH JOIN (ANTI) (Cost=5 Card=77 Bytes=8K)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)
   3    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=4 Bytes=52)


MERGE ANTI-JOIN
ex) select * from emp where  empno is not null
   and (empno) not in (select /*+ merge_aj*/ empno from emp_sub_20 where empno is not null)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=77 Bytes=8K)
   1    0   MERGE JOIN (ANTI) (Cost=8 Card=77 Bytes=8K)
   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 (UNIQUE) (Cost=4 Card=4 Bytes=52)
   5    4       TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=4 Bytes=52)


[HASH | MERGE] SEMI JOIN
semi join은 보통 exists절을 이용하는 correlated exists 구문에서 나타난다.

BEFORE) select * from emp x1 where  exists (select 1 from emp_sub_20 where empno = x1.empno)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     INDEX (RANGE SCAN) OF 'EMP_SUB_EMPNO' (NON-UNIQUE)

drop index emp_sub_empno ;

AFTER 1) select * from emp x1 where
   exists (select 1 from emp_sub_20 where empno = x1.empno)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20'

AFTER 2) select * from emp x1 where   exists (select /*+ hash_sj*/1 from emp_sub_20 where empno = x1.empno)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=8K)
   1    0   HASH JOIN (SEMI) (Cost=5 Card=82 Bytes=8K)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)
   3    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=82 Bytes=1K)

create index emp_sub_empno on emp_sub_20(empno) ;


HASH SEMI-JOIN
ex) select * from emp x1 where
    empno is not null
    and exists(select /*+ hash_sj*/ 1 from emp_sub_20 where empno = x1.empno and empno is not null)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=5 Bytes=500)
   1    0   HASH JOIN (SEMI) (Cost=5 Card=5 Bytes=500)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)
   3    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=4 Bytes=52)


MERGE SEMI-JOIN
ex) select * from emp x1 where  empno is not null
    and exists(select /*+ merge_sj*/ 1 from emp_sub_20 where empno = x1.empno and empno is not null)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=5 Bytes=500)
   1    0   MERGE JOIN (SEMI) (Cost=8 Card=5 Bytes=500)
   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 (UNIQUE) (Cost=4 Card=4 Bytes=52)
   5    4       TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=4 Bytes=52)


OUTER JOIN
Outer join은 Nested Loops, Merge Join, Hash Join과 수반되어 나타나는 옵션의 성격을 가지는 조인방법이다.
아우터 조인에서는 (+)가 붙지 않는 컬럼을 가진 테이블이 무조건 드라이빙 테이블이 된다.

ex) select * from emp x1, emp_sub_20 x2 where x1.empno = x2.empno(+)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS (OUTER)
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP_SUB_20'
   4    3       INDEX (RANGE SCAN) OF 'EMP_SUB_EMPNO' (NON-UNIQUE)

ex) select /*+ use_merge(x1 x2)*/*  from emp x1, emp_sub_20 x2  where x1.empno = x2.empno(+)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=82 Bytes=9K)
   1    0   MERGE JOIN (OUTER) (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 'EMP_SUB_20' (Cost=2 Card=82 Bytes=2K)

ex) select /*+ use_hash(x1 x2)*/*  from emp x1, emp_sub_20 x2  where x1.empno = x2.empno(+)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9K)
   1    0   HASH JOIN (OUTER) (Cost=5 Card=82 Bytes=9K)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7K)
   3    1     TABLE ACCESS (FULL) OF 'EMP_SUB_20' (Cost=2 Card=82 Bytes=2K)

# ROW OPERATION

CONCATENATION
ex) select * from emp x1 where job = 'CLERK' and deptno in (10, 20)
-- where (job = 'CLERK' and deptno = 10 ) or (job = 'CLERK' and deptno = 20 )

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   3    2       AND-EQUAL
   4    3         INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)
   5    3         INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (NON-UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   7    6       AND-EQUAL
   8    7         INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)
   9    7         INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (NON-UNIQUE)

CONNECT BY

ex) create index emp_mgr_idx on emp(mgr, empno) ;
select * from emp x1 connect by mgr = prior empno start with mgr = 7839
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS
   3    2       INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)
   4    2       TABLE ACCESS (BY USER ROWID) OF 'EMP'
   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   9    8         INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)

ex) select * from emp x1 connect by mgr + 0 = prior empno start with mgr = 7839
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS
   3    2       INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)
   4    2       TABLE ACCESS (BY USER ROWID) OF 'EMP'
   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       TABLE ACCESS (FULL) OF 'EMP'


COUNT [STOP KEY]
ex) select rownum, ename from emp --rownum이 select column list에 나올때
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT
   2    1     TABLE ACCESS (FULL) OF 'EMP'

PSEUDO COLUMN CURRVAL, NEXTVAL, LEVEL, ROWID, ROWNUM

ex) select * from emp where rownum <= 3 --rownum이 where 절에 나올때
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF 'EMP'


FILTER
ex) select * from emp x1 where sal = (select min(sal) from emp t1 where x1.deptno = t1.deptno)
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     SORT (AGGREGATE)
   4    3       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   5    4         INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

ex) select * from emp x1 where deptno = 20 connect by mgr = prior empno start with mgr = 7839
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     CONNECT BY (WITH FILTERING)
   3    2       NESTED LOOPS
   4    3         INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)
   5    3         TABLE ACCESS (BY USER ROWID) OF 'EMP'
   6    2       NESTED LOOPS
   7    6         BUFFER (SORT)
   8    7           CONNECT BY PUMP
   9    6         TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
  10    9           INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)

FOR UPDATE
ex) select * from emp for update ;
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FOR UPDATE
   2    1     TABLE ACCESS (FULL) OF 'EMP'




# SET OPERATION

SORT [JOIN | GROUP BY | ORDER BY | UNIQUE]

SORT JOIN
Merge Join시에 수반되는 집합연산이다. 즉 머지 조인은 항상 SORT 연산을 동반한다.

ex) select /*+ use_merge(e d)*/ * from emp e, dept d where e.deptno = d.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)


SORT (GROUP BY)
Group by 절이 사용되어 그루핑 기능이 수행될때 나타나는 집합연산이다.

ex) select job, count(*) from emp group by job
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     TABLE ACCESS (FULL) OF 'EMP'

SORT(ORDER BY)
Order by절이 사용되여 결과셋을 정렬하는 집합연산이다.

ex) select * from emp e order by deptno
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (FULL) OF 'EMP'

참고로 인덱스가 있는 경우 select * from emp e where deptno > 0 와 같이 집합연산을 로우 연산으로 바꿀 수 있다.
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

SORT(UNIQUE)
minus, intersection, union, distinct 등이 사용되는 집합연산으로 결과 셋을 정렬하여 중복 레코드를 제거하는 기능을 한다.

ex) select distinct * from emp e
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (FULL) OF 'EMP'

SORT (AGGREGATE)
Group by 절이 쓰이지 않으면서, 쿼리 내에서 max, min, sum, count, avg와 같은 그루핑 함수가 사용됐을때 나타나는 집합연산이다.

ex) select avg(sal) from emp
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP'


ITERSECTION
ex) select empno, ename from emp e intersect select empno, ename from emp_sub_20
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INTERSECTION
   2    1     SORT (UNIQUE)
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    1     SORT (UNIQUE)
   5    4       TABLE ACCESS (FULL) OF 'EMP_SUB_20'


MINUS
ex) select empno, ename from emp where empno > 7000 minus select empno, ename from emp_sub_20
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MINUS
   2    1     SORT (UNIQUE)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   4    3         INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE)
   5    1     SORT (UNIQUE)
   6    5       TABLE ACCESS (FULL) OF 'EMP_SUB_20'

UNION
union all 연산에 중복레코드를 제거하는 sort unique 연산이 더 추가되어 있는 집합연산이다.

ex) select empno, ename from emp where empno > 7000 union  select empno, ename from emp_sub_20
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   4    3         INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE)
   5    2       TABLE ACCESS (FULL) OF 'EMP_SUB_20'


# SPECIAL CASE

REMOTE
ex) select * from emp e, dept@rlink d where d.deptno = e.deptno
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9K)
   1    0   NESTED LOOPS
   2    1     REMOTE*                                                                                 RLINK
   3    1     TABLE ACCESS (BY INDEX ROWID) OF EMP
   4    3     INDEX(RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

보통 원격지 테이블과 NL로 풀리는 실행계획은 성능측면에서 좋지 못하다

SEQUENCE
ex) select seq_emp.nextval from dual

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SEQUENCE OF 'SEQ_EMP'
   2    1     TABLE ACCESS (FULL) OF 'SYS.DUAL'


PARTITION(SINGLE / ITERATOR / ALL / INLIST)
TABLE ACCESS CLUSTER
TABLE ACCESS HASH
BITMAP INDEX
BITMAP AND | OR | MERGE
VIEW

bigSizeTable에는 약 1억건의 row가 있다. 다음의 쿼리를 튜닝할 수 있는 방법을 최소 3가지 이상 나열하라.(난이도 C)



select count(*) from bigsizeTable


SELECT STATEMENT 
  TABLE ACCESS FULL BIGSIZETABLE

문) SQL로 100 ! (100의 팩토리얼)을 구하라 (난이도 C)




물론
select 100 * 99 *98 * 97  ..... * 2
from dual

이런식의 방법은 우아하지 못함을 떠나서 아마도 수치연산 오류로 제대로 작동하지 않을테니 굳이 시험해 보지 않아도 된다. 정확히 기억나지 않지만 오라클의 경우 최대 연산 가능한 수는 10^128 보다 작다.

1. 사원의 수가 아주 많아서 NL조인으로는 충분한 전체범위 처리 속도를 얻기 어렵다. 아래 SQL을 Hash Join 실행계획을 사용하도록 유도하라. (난이도 C+)


select a.ename, a.sal, b.grade
from emp a, sal_grade b
where a.sal between b.minValue and b.maxValue



- 기대되는 실행계획 :

SELECT STATEMENT
HASH JOIN
  TABLE ACCESS FULL EMP
  TABLE ACCESS FULL SAL_GRADE

row의 연속성을 구현하는 문제이다.

1. 아래와 같은 입력 테이블이 있다. 연속일로 매출이 발생한 정보를 출력하라. (난이도 C)

매출년일        수량
20090130         30
20090131         10
20090201         25
20090202         20
20090204         35
20090205         40
20090206         10
20090209         20
20090211         35


기대되는 출력
기간                           수량
20090130 - 20090202      85
20090204 - 20090206      85
20090209 - 20090209      20
20090211 - 20090211      30



2. 같은 입력으로 토요일과 일요일은 매출이 없어도 휴일이기 때문에(일반 국경일은 패스) 연속성을 가지는 걸로 가정하고 출력. 즉 금요일에 매출이 발생하고 다음 월요일에 매출이 발생하면 연속성을 가지는 걸로 간주한다. (난이도 C+)

기대되는 출력
기간                           수량
20090130 - 20090202      85
20090204 - 20090209      105
20090211 - 20090211      30

열렬한 팬사이트 중의 하나였던 엔코아에서 작년에 SQL Quiz 게시판을 없애버렸다. 참여율이 저조해서인지 아니면 다른 이유가 있는지는 모르겠지만 개인적으로는 아쉽기 그지 없었다. 그래서 개인적으로 재미삼아 퀴즈문제를 만들어 보기로 했다 -ㅅ-  사실 이 블로그 자체가 남에게 보여주기 위함보다 그냥 개인적인 목적으로 만든 블로그지만 - 그동안 하도 글들을 날려먹어서 -ㅅ- 머 어쩌랴 싶기도 하다. 퀴즈는 아마도 이 블로그에서 유일하게 타인만을 위한(정말?) 글이 될 예정...

웬일인지 개인적으로 면접을 본 횟수보다 면접을 한 횟수가 10배정도 많은데 그 때마나 DB파트에서는 꼭 SQL 문제를 내곤 했다. 단순한 암기적 지식이 필요한 질문은 면접 질문중 최악이지만 SQL은 암기적 지식이 필요 없고 평소 RDMS를 제대로 관계형 패러다임으로 인식하고 있는지 알아볼 수 있기 때문이다. 문제별 난이도는 주관적이고 상대적이다.

1. 해당년원을 인풋으로 받아서 SQL을 사용하여 달력을 출력하기 (난이도 D)


ex)
입력 : 200801

출력 :

일 월 화 수 목  금  토
         1   2  3    4   5
6   7   8  9  10  11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31




1-1 입력은 같지만 이전달과 이후달의 날짜도 같이 출력하기. (난이도 C)


입력 : 200801

출력 :

일 월 화 수 목  금  토
30  31 1  2  3    4   5
6   7   8  9  10  11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31  1  2