by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 서브쿼리 Unnesting




02. 서브쿼리 Unnesting

(1) 서브쿼리의 분류

서브쿼리 - 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록(Query Block).

서브쿼리 분류
1. 인라인 뷰(Inline View) : from 절에 나타나는 서브쿼리.
2. 중첩된 서브쿼리(Nested Subquery) : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리.
서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 '상관관계가 있는(Correlated) 서브쿼리'라 함.
3. 스칼라 서브쿼리(Scalar Subquery) : 한 레코드당 정확히 하나의 컬럼 값만을 리턴하는 것이 특징. 주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 컬럼이 올 수 있는 대부분 위치에서 사용 가능하다.

  • 옵티마이저는 쿼리 블록 단위로 최적화 수행.
    쿼리블록 단위로 최적의 액세스 경로와 조인순서, 조인방식을 선택하는 것을 목표로 한다.
  • 각 서브쿼리를 최적화했다고 해서 쿼리 전체가 최적화됐다고 말할 수는 없다.
    옵티마이저가 숲을 바라보는 시각으로 쿼리를 이해하려면 먼저 서브쿼리를 풀어내야만 한다.

서브쿼리를 풀어내는 두 가지 쿼리 변환

  • 서브쿼리Unnesting => 중첩된 서브쿼리와 관련.
  • 뷰Merging => 인라인뷰와 관련.

(2) 서브쿼리 Unnesting의 의미

nest : 상자 등을 차곡차곡 포개넣다. 중첩
unnest : 중첩된 상태를 풀어낸다.
서브쿼리 Unnesting - 중첩된 서브쿼리를 풀어내는 것.

select * 
  from emp a
 where exists (
               select 'x'
                 from dept c
                where c.deptno = a.deptno
              )
  and sal > (
              select avg(sal) 
                from emp b
               where exists (
                              select 'x' 
                                from salgrade d
                               where b.sal between d.losal and d.hisal
                 and d.grade = 4)
            )

p467 그림 4-2 참조

중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다.
따라서 논리적인 관점에서 그 처리과정은 IN, Exists를 불문하고 필터 방식이어야 한다.
즉, 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.
하지만 서브쿼리를 처리하는 데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래 둘 중 하나를 선택한다.
1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 이를 일컬어 '서브쿼리 Unnesting'이라고 한다.
2. 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터(Filter) 오퍼레이션이 나타난다.

서브쿼리의 또다른 최적화 기법

where 조건절에사용된 서브쿼리가
1.메인쿼리와 상관관계에 있지 않으면서
2. 단일 로우를 리턴하는 아래와 같은 형태의 서브쿼리를 처리할 때 나타나는 방식이다.
(첫번째는 스칼라서브쿼리지만, 두번째는 두 개 컬럼을 리턴하므로 스칼라서브쿼리가 아니다)

select *
from tab1
where key1 = (select avg(col1)
from tab2);

select *
from tab1
where (key1,key2) = (select col1,col2
from tab2
where col3 >= 5000
and rownum = 1);

위와 같은 형태의 서브쿼리는 Fetch가 아닌 Excute시점에 먼저 수행하고, 그 결과값을 메인쿼리에 상수로 제공하는 아래와 같은 방식으로 수행한다.

select *
from tab1
where key1 = :value1;

select *
from tab1
where (key1,key2) = (:value1,:value2);

(3) 서브쿼리 Unnesting의 이점

서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
서브쿼리 Unnesting과 관련한 힌트
1. unnest : 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
2. no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.

(4) 서브쿼리 Unnesting 기본 예시

select *
  from emp
 where deptno in (select /*+ no_unnest */ deptno from dept)
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|*  1 |  FILTER            |         |      1 |        |     14 |00:00:00.01 |      12 |
|   2 |   TABLE ACCESS FULL| EMP     |      1 |     14 |     14 |00:00:00.01 |       9 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------
                                                                                        
Predicate Information (identified by operation id):                                     
---------------------------------------------------                                     
                                                                                        
1 - filter( IS NOT NULL)                                                                
3 - access("DEPTNO"=:B1)                                                                
                                                                                        

21 rows selected.

unnest 힌트를 사용하거나 옵티마이저가 스스로 Unnesting을 선택한다면, 변환된 쿼리는 아래와 같은 조인문 형태가 된다.
select *
  from (select deptno from dept) a, emp b
 where b.deptno = a.deptno

Execution Plan
-------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=560)
   1    0   NESTED LOOPS (Cost=3 Card=14 Bytes=560)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=3)

이것은 다시 뷰Merging과정을 거쳐 최종적으로 아래와 같은 형태가 된다.
select emp.* 
  from dept, emp
 where emp.deptno = dept.deptno 

Execution Plan
-------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=518)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)

=> 결과는 같겠으나 실행계획은 서로 다르게 나옴.

=> 서브쿼리에 UNNEST 힌트를 주고 실행계획을 확인한 결과.

select *
  from emp
 where deptno in (select /*+ unnest */ deptno from dept)

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |    14 |   560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPTNO"="DEPTNO")
                                                                             
- 책하고 약간 다름

(5) Unnesting된 쿼리의 조인 순서 조정

Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느 쪽이든 드라이빙 집합으로 선택될 수 있다.
선택은 옵티마이저의 몫이며, 판단 근거는 데이터 분포를 포함한 통계정보에 있다.
emp 테이블이 드라이빙된 경우된 경우는 아래와 같다.



------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |    14 |   560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPTNO"="DEPTNO")

Unnesting된 쿼리의 조인 순서를 조정하는 방법

=> 메인쿼리 집합을 먼저 드라이빙 하는 것(leading힌트 사용)
select /*+ leading(emp) */ * from emp 
where deptno in (select /*+ unnest */ deptno from dept);

Execution Plan
------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=560)
   1    0   NESTED LOOPS (Cost=3 Card=14 Bytes=560)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=3)

=> 서브쿼리 집합을 먼저 드라이빙 하는 경우
(원래는 아래와 같이 하면 메인쿼리에서 서브쿼리쪽 테이블을 참조할 수 없으나 10g부터는 조인순서가 조정됨.)
select /*+ leading(dept) */ * from emp 
where deptno in (select /*+ unnest */ deptno from dept);

Execution Plan
------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=14 Bytes=560)
   1    0   HASH JOIN (Cost=5 Card=14 Bytes=560)
   2    1     INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=12)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)


=>leading 힌트 대신 ordered힌트를 사용.
select /*+ ordered */ * from emp 
where deptno in (select /*+ unnest */ deptno from dept);

Execution Plan
------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=14 Bytes=560)
   1    0   HASH JOIN (Cost=5 Card=14 Bytes=560)
   2    1     INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=12)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)

=> qb_name힌트 사용으로 쉽고 정확하게 제어(10g)
select /*+ leading(dept@qb1) */ * from emp 
where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept);

Execution Plan
------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=14 Bytes=560)
   1    0   HASH JOIN (Cost=5 Card=14 Bytes=560)
   2    1     INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=12)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)

(6) 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때

지금까지 본 예제는 M:1이었기 때문에 일반 조인문으로 바꾸더라도 쿼리결과가 보장됨.
만약, 서브쿼리쪽 테이블이 조인 컬럼에 PK/Unique 제약 또는 Unique 인덱스가 없다면??
<사례1>

select * 
  from dept
 where deptno in (select deptno from emp)
=> 다음과 같이 바꾸게 되면 결과오류
select * 
  from (select deptno from emp) a, dept b
 where b.deptno = a.deptno

<사례2>

select * 
  from emp
 where deptno in (select deptno from dept)

=> 조인문으로 바꾸더라도 결과 오류는 생기지 않으나 PK/Unique 제약 또는 Unique 인덱스가 없다면 결과를 확신할 수 없으니 변환을 시도하지 않는다.

  • 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
  • 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인(Semi Join)방식으로 조인한다. 이것이 세미조인이 탄생하게 된 배경이다.

Sort Unique 오퍼레이션 수행

  • dept 테이블에 PK 제약을 제거하고 deptno컬럼에 Nonunique 인덱스를 생성하고나서 실행 계획을 다시 확인.
=> 책내용
alter table dept drop primary key;

create index dept_deptno_idx on dept(deptno);

exec dbms_stats.gather_table_stats(user, 'dept');

select * from emp
where  deptno in (select deptno from dept);
=> 실제 테스트한 내용
create table emp1 as select * from emp;

create table dept1 as select * from dept;

create index dept1_deptno_idx on dept1(deptno);

exec dbms_stats.gather_table_stats('scott', 'emp1');
exec dbms_stats.gather_table_stats('scott', 'dept1');

select * from emp1
where  deptno in (select deptno from dept1);

Execution Plan
---------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=560)
   1    0   NESTED LOOPS (SEMI) (Cost=3 Card=14 Bytes=560)
   2    1     TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=518)
   3    1     INDEX (RANGE SCAN) OF 'DEPT1_DEPTNO_IDX' (INDEX) (Cost=0 Card=4 Bytes=12)

실제로 dept 테이블은 Unique한 집합이지만 옵티마이저는 확신할 수 없어 sort unique 오퍼레이션을 수행함.(아래와 같은 형태로 변환됨- 책에는 이렇게 나왔지만 실제는 다름.)

select b.*
from  (select /*+ no_merge */ distinct deptno from dept1 order by deptno) a, emp1 b
where  b.deptno = a.deptno ;

Execution Plan
----------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=14 Bytes=700)
   1    0   HASH JOIN (Cost=9 Card=14 Bytes=700)
   2    1     VIEW (Cost=5 Card=4 Bytes=52)
   3    2       SORT (UNIQUE) (Cost=4 Card=4 Bytes=12)
   4    3         TABLE ACCESS (FULL) OF 'DEPT1' (TABLE) (Cost=3 Card=4 Bytes=12)
   5    1     TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=518)

다음과 같이 힌트 사용으로 유도 가능함


-- 오라클 9i
select /*+ ordered use_nl(emp) */ * from emp
where  deptno in (select /*+ unnest */ deptno from dept);

-- 오라클 10g 이후
select /*+ leading(dept@qb1) use_nl(emp) */ * from emp 
where deptno in (select /*+ unnest  qb_name(qb1) */ deptno from dept);

Execution Plan
----------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=560)
   1    0   NESTED LOOPS (SEMI) (Cost=3 Card=14 Bytes=560)
   2    1     TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=518)
   3    1     INDEX (RANGE SCAN) OF 'DEPT1_DEPTNO_IDX' (INDEX) (Cost=0 Card=4 Bytes=12)

책에서 얘기하는 형태로 실행계획이 수립되지는 않았지만 논점은 그 부분이 아니기 때문에 넘어가기로 함. 하지만 힌트를 사용하여 원하는 대로 실행계획을 만들 수는 있다.

세미 조인 방식으로 수행

select /*+ leading(emp) */ * from emp
where  deptno in (select /*+ unnest nl_sj */ deptno from dept);

Execution Plan
---------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=560)
   1    0   NESTED LOOPS (SEMI) (Cost=3 Card=14 Bytes=560)
   2    1     TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=518)
   3    1     INDEX (RANGE SCAN) OF 'DEPT1_DEPTNO_IDX' (INDEX) (Cost=0 Card=4 Bytes=12)

select /*+ leading(emp) */ * from emp
where  deptno in (select /*+ unnest hash_sj */ deptno from dept);

Execution Plan
-----------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=14 Bytes=560)
   1    0   HASH JOIN (SEMI) (Cost=5 Card=14 Bytes=560)
   2    1     TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=518)
   3    1     INDEX (FULL SCAN) OF 'DEPT1_DEPTNO_IDX' (INDEX) (Cost=1 Card=4 Bytes=12)


select /*+ leading(emp) */ * from emp
where  deptno in (select /*+ unnest merge_sj */ deptno from dept);

Execution Plan
------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=14 Bytes=560)
   1    0   MERGE JOIN (SEMI) (Cost=6 Card=14 Bytes=560)
   2    1     SORT (JOIN) (Cost=4 Card=14 Bytes=518)
   3    2       TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=518)
   4    1     SORT (UNIQUE) (Cost=2 Card=4 Bytes=12)
   5    4       INDEX (FULL SCAN) OF 'DEPT1_DEPTNO_IDX' (INDEX) (Cost=1 Card=4 Bytes=12)

세미조인의 장점은 NL세미조인, 해시세미조인, 소트머지 세미조인 가능.

(7) 필터 오퍼레이션과 세미조인의 캐싱 효과

서브쿼리를 Unnesting하지 않으면 쿼리를 최적화 하는데 있어 불리한 경우가 많아.
메인 쿼리를 수행하면서 건건이 서브쿼리를 반복수행하는 필터오퍼레이션을 사용하기 때문이다.
대량 집합을 Random액세스 방식으로 서브쿼리 집합을 필터링 한다면 빠른 수행속도를 얻을 수 없다.
그러나 2장 6절의 스칼라서브쿼리의 캐싱 효과와 같이 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면 저장된 값을 출력하는 방식을 사용한다면 빠른 수행 속도를 얻을 수 있다.(필터최적화 기법-캐싱)

조나단루이스 => 8i,9i:256개, 10g:1024개 해시 엔트리를 캐싱한다고 함.

create table dept as select * from scott.dept;

alter table dept add constraint dept_pk primary key(deptno);

create table t_emp
as
select *
from   scott.emp
     ,(select rownum no from dual connect by level <= 100);


exec dbms_stats.gather_table_stats(user, 'emp');

exec dbms_stats.gather_table_stats(user, 'dept');

select count(*) from t_emp t
where exists (select /*+ no_unnest */ 'x' from dept
              where deptno = t.deptno and loc is not null);
              
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.001          0         18          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.002          0         18          0          1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=18 pr=0 pw=0 time=1114 us)
   1400    FILTER  (cr=18 pr=0 pw=0 time=11292 us)
   1400     TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=2847 us)
      3     TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=62 us)
      3      INDEX UNIQUE SCAN PK_DEPT (cr=3 pr=0 pw=0 time=29 us)(Object ID 51250)


select count(*) from t_emp t
where exists (select /*+ unnest nl_sj */ 'x' from dept
              where deptno = t.deptno and loc is not null);

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.016        0.001          0         17          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.016        0.002          0         17          0          1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=17 pr=0 pw=0 time=921 us)
   1400    NESTED LOOPS SEMI (cr=17 pr=0 pw=0 time=12693 us)
   1400     TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=2848 us)
      3     TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=0 pw=0 time=65 us)
      3      INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=30 us)(Object ID 51250)

=> 필터오퍼레이션일 경우 해싱효과가 있음. 
nl세미조인에서도 10g부터는 해싱효과가 있음.(9i까지는 없었음)

(8) Anti 조인

not exists, not in서브쿼리도 Unnesting 하지 않으면 다음과 같이 필터 방식으로 처리된다.

select * from dept d
where  not exists 
  (select /*+ no_unnest */'x' from emp where deptno = d.deptno);

Execution Plan
----------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=60)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=80)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=2 Bytes=6)

select * from dept d
where  not exists 
  (select /*+ unnest nl_aj */'x' from emp where deptno = d.deptno);


Execution Plan
-----------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=23)
   1    0   NESTED LOOPS (ANTI) (Cost=9 Card=1 Bytes=23)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=80)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=9 Bytes=27)


select * from dept d
where  not exists 
  (select /*+ unnest merge_aj */ 'x' from emp where deptno = d.deptno);

Execution Plan
------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=23)
   1    0   MERGE JOIN (ANTI) (Cost=6 Card=1 Bytes=23)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   3    2       INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4)
   4    1     SORT (UNIQUE) (Cost=4 Card=14 Bytes=42)
   5    4       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)

select * from dept d
where  not exists 
  (select /*+ unnest hash_aj */ 'x' from emp where deptno = d.deptno);

Execution Plan
-------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=23)
   1    0   HASH JOIN (ANTI) (Cost=7 Card=1 Bytes=23)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=80)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)

(9) 집계 서브쿼리 제거

집계함수를 포함하는 서브쿼리를 Unnesting하고, 이를 다시 분석함수로 대체하는 쿼리변환이 10g에서 도입.

select d.deptno, d.dname, e.empno, e.ename, e.sal
from   emp e, dept d
where  d.deptno = e.deptno
and    e.sal >= (select avg(sal) from emp where deptno = d.deptno) 
;

=> 위의 쿼리를 Unnesting하면 다음의 쿼리가 만들어짐.
select d.deptno, d.dname, e.empno, e.ename, e.sal
from  (select deptno, avg(sal) avg_sal from emp group by deptno) x, emp e, dept d
where  d.deptno = e.deptno
and    e.deptno = x.deptno
and    e.sal >= x.avg_sal
;

=> 위의 쿼리를 한번더 변환을 시도해 인라인뷰를 Merging하거나 그대로 둔 채 최적화 할 수 있다.

(10) Pushing 서브쿼리

=> 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것
=> push_subq 힌트 사용.
=> Pushing 서브쿼리는 Unnesting 되지 않은 서브쿼리에만 작동함.
=> 따라서 push_subq 는 no_unnest힌트와 같이 사용해야 함.

*지난 스터디때 나온 의문점 테스트 *
=> 힌트를 우선적으로 따르지 않는 경우, 즉 "서브쿼리에 unnest와 push_subq를 같이 기술한 경우" 가 아닌 경우에 실행계획이 어떻게 풀리는지 테스트.

select  * from dept d
where  not exists 
  (select  'x' from emp where deptno = d.deptno);

Execution Plan
---------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=23)
   1    0   MERGE JOIN (ANTI) (Cost=6 Card=1 Bytes=23)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   3    2       INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4)
   4    1     SORT (UNIQUE) (Cost=4 Card=14 Bytes=42)
   5    4       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)


select /*+ push_subq */ * from dept d
where  not exists 
  (select  'x' from emp where deptno = d.deptno);

Execution Plan
-----------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=23)
   1    0   MERGE JOIN (ANTI) (Cost=6 Card=1 Bytes=23)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   3    2       INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4)
   4    1     SORT (UNIQUE) (Cost=4 Card=14 Bytes=42)
   5    4       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)

select /*+ push_subq */ * from dept d
where  not exists 
  (select /*+ unnest */'x' from emp where deptno = d.deptno);

Execution Plan
-------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=23)
   1    0   MERGE JOIN (ANTI) (Cost=6 Card=1 Bytes=23)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   3    2       INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4)
   4    1     SORT (UNIQUE) (Cost=4 Card=14 Bytes=42)
   5    4       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)

select /*+ push_subq */ * from dept d
where  not exists 
  (select /*+ no_unnest */'x' from emp where deptno = d.deptno);

Execution Plan
-------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=60)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=80)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=2 Bytes=6)

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.