서브쿼리 - 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록(Query Block).
서브쿼리 분류
1. 인라인 뷰(Inline View) : from 절에 나타나는 서브쿼리.
2. 중첩된 서브쿼리(Nested Subquery) : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리.
서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 '상관관계가 있는(Correlated) 서브쿼리'라 함.
3. 스칼라 서브쿼리(Scalar Subquery) : 한 레코드당 정확히 하나의 컬럼 값만을 리턴하는 것이 특징. 주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 컬럼이 올 수 있는 대부분 위치에서 사용 가능하다.
서브쿼리를 풀어내는 두 가지 쿼리 변환
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);
서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
서브쿼리 Unnesting과 관련한 힌트
1. unnest : 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
2. no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.
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")
- 책하고 약간 다름
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)
지금까지 본 예제는 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 인덱스가 없다면 결과를 확신할 수 없으니 변환을 시도하지 않는다.
=> 책내용
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세미조인, 해시세미조인, 소트머지 세미조인 가능.
서브쿼리를 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까지는 없었음)
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)
집계함수를 포함하는 서브쿼리를 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하거나 그대로 둔 채 최적화 할 수 있다.
=> 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것
=> 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)