Database - Plan 이해

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

실행계획의 해석 순서

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