drop table emp purge;
drop table dept purge;
create table emp
nologging
as
select * from scott.emp, (select * from dual connect by level <= 10000);
create table dept
nologging
as
select * from scott.dept;
create index emp_dept_x01 on emp (deptno);
alter table dept add constraint dept_pk primary key (deptno);
exec dbms_stats.gather_table_stats(user, 'emp');
exec dbms_stats.gather_table_stats(user, 'dept');
-- nested loop semi join으로 수행 - semi join caching 효과 발생함
explain plan for
select /* A */ /*+ leading(a) */ count(*)
from emp a
where exists (select /*+ nl_sj */ 'x'
from dept b
where a.deptno = b.deptno);
@cplan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 85 (28)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | NESTED LOOPS SEMI | | 138K| 813K| 85 (28)|
| 3 | INDEX FAST FULL SCAN| EMP_DEPT_X01 | 138K| 406K| 65 (5)|
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 4 | 12 | 0 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.03 0 283 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 0 283 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=283 pr=0 pw=0 time=30342 us)
140000 NESTED LOOPS SEMI (cr=283 pr=0 pw=0 time=1539989 us)
140000 INDEX FAST FULL SCAN EMP_DEPT_X01 (cr=281 pr=0 pw=0 time=699995 us)(object id 54055)
3 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=0 us)(object id 54056) --semi join에 대한 캐시효과가 나타남
/** --9i test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.67 3.59 274 283 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.67 3.61 274 283 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=283 r=274 w=0 time=3595371 us)
140000 NESTED LOOPS SEMI (cr=283 r=274 w=0 time=3003332 us)
140000 INDEX FAST FULL SCAN EMP_DEPT_X01 (cr=281 r=274 w=0 time=612420 us)(object id 40285)
140000 INDEX UNIQUE SCAN DEPT_PK (cr=2 r=0 w=0 time=630992 us)(object id 40286) --semi join에 대한 캐시효과가 없음(그러나 buffer pinning 효과는 있음)
**/
-- hash semi join으로 수행
explain plan for
select /* B */ /*+ leading(a) */ count(*)
from emp a
where exists (select /*+ hash_sj */ 'x'
from dept b
where a.deptno = b.deptno);
@cplan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | | 183 (4)|
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN SEMI | | 138K| 813K| 2040K| 183 (4)|
| 3 | INDEX FAST FULL SCAN| EMP_DEPT_X01 | 138K| 406K| | 65 (5)|
| 4 | INDEX FULL SCAN | DEPT_PK | 4 | 12 | | 1 (0)|
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPTNO"="B"."DEPTNO")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.44 0.46 105 282 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.44 0.46 105 282 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=282 pr=105 pw=105 time=463123 us)
140000 HASH JOIN SEMI (cr=282 pr=105 pw=105 time=2891982 us)
140000 INDEX FAST FULL SCAN EMP_DEPT_X01 (cr=281 pr=0 pw=0 time=4340347 us)(object id 54055)
3 INDEX FULL SCAN DEPT_PK (cr=1 pr=0 pw=0 time=62 us)(object id 54056)
--hash right semi join 확인(dept 쪽을 먼저 driving 하는 경우) - 10g 부터 지원함
explain plan for
select /* B */ /*+ leading(@xxx) */ count(*)
from emp a
where exists (select /*+ hash_sj unnest qb_name(xxx) */ 'x'
from dept b
where a.deptno = b.deptno);
@cplan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 68 (8)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|* 2 | HASH JOIN RIGHT SEMI | | 139K| 819K| 68 (8)|
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | 12 | 1 (0)|
| 4 | INDEX FAST FULL SCAN| EMP_DEPT_X01 | 139K| 409K| 65 (5)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPTNO"="B"."DEPTNO")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.32 0.32 0 282 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.32 0.32 0 282 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=282 pr=0 pw=0 time=326364 us)
140000 HASH JOIN RIGHT SEMI (cr=282 pr=0 pw=0 time=19322968 us)
4 INDEX FULL SCAN DEPT_PK (cr=1 pr=0 pw=0 time=670 us)(object id 54066)
140000 INDEX FAST FULL SCAN EMP_DEPT_X01 (cr=281 pr=0 pw=0 time=8121468 us)(object id 54055)
-- merge semi join으로 수행
explain plan for
select /* C */ /*+ leading(a) */ count(*)
from emp a
where exists (select /*+ merge_sj */ 'x'
from dept b
where a.deptno = b.deptno);
@cplan
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 281 (2)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | MERGE JOIN SEMI | | 138K| 813K| 281 (2)|
| 3 | INDEX FULL SCAN | EMP_DEPT_X01 | 138K| 406K| 279 (2)|
|* 4 | SORT UNIQUE | | 4 | 12 | 2 (50)|
| 5 | INDEX FULL SCAN| DEPT_PK | 4 | 12 | 1 (0)|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.72 3.63 0 276 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.72 3.64 0 276 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=276 pr=0 pw=0 time=3637062 us)
140000 MERGE JOIN SEMI (cr=276 pr=0 pw=0 time=8120681 us)
140000 INDEX FULL SCAN EMP_DEPT_X01 (cr=275 pr=0 pw=0 time=1680334 us)(object id 54055)
140000 SORT UNIQUE (cr=1 pr=0 pw=0 time=2338513 us)
4 INDEX FULL SCAN DEPT_PK (cr=1 pr=0 pw=0 time=178 us)(object id 54056)
-- filter 방식으로 처리 - filter caching 효과 발생함
explain plan for
select /* D */ count(*)
from emp a
where a.deptno in (select /*+ no_unnest */ b.deptno
from dept b);
@cplan
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 203 (4)|
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL| EMP | 138K| 406K| 203 (4)|
|* 4 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 3 | 0 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "B" WHERE
"B"."DEPTNO"=:B1))
4 - access("B"."DEPTNO"=:B1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.21 0.20 0 873 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.21 0.21 0 873 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=873 pr=0 pw=0 time=207240 us)
140000 FILTER (cr=873 pr=0 pw=0 time=6720240 us)
140000 TABLE ACCESS FULL EMP (cr=870 pr=0 pw=0 time=3500084 us)
3 INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=125 us)(object id 54056)
-- in절을 이용하더라도 unnest 하면 join 으로 처리됨
explain plan for
select /* E */ /*+ leading(a) */ count(*)
from emp a
where a.deptno in (select /*+ unnest use_nl(b) */ b.deptno from dept b);
@cplan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 85 (28)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | NESTED LOOPS | | 138K| 813K| 85 (28)|
| 3 | INDEX FAST FULL SCAN| EMP_DEPT_X01 | 138K| 406K| 65 (5)|
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 | 3 | 0 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.33 5.20 0 283 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.33 5.21 0 283 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=283 pr=0 pw=0 time=5208983 us)
140000 NESTED LOOPS (cr=283 pr=0 pw=0 time=7559948 us)
140000 INDEX FAST FULL SCAN EMP_DEPT_X01 (cr=281 pr=0 pw=0 time=1679989 us)(object id 54055)
140000 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=3476079 us)(object id 54056)
-- in절을 이용하는경우 sort unique가 발생하는경우는 unnest되면서 서브쿼리의 집합에 대해서 unique 보장을 할 수 없을경우
-- 서브쿼리가 no_unnest 되면 filter 방식으로 처리됨
alter table dept drop primary key;
create index dept_x01 on dept (deptno);
explain plan for
select /* F */ /*+ leading(b@subq) use_nl(a) */ count(*)
from emp a
where a.deptno in (select /*+ qb_name(subq) unnest */ b.deptno from dept b);
@cplan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 129 (5)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | NESTED LOOPS | | 138K| 813K| 129 (5)|
| 3 | SORT UNIQUE | | 4 | 12 | 1 (0)|
| 4 | INDEX FULL SCAN | DEPT_X01 | 4 | 12 | 1 (0)|
|* 5 | INDEX FAST FULL SCAN| EMP_DEPT_X01 | 34704 | 101K| 64 (5)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("A"."DEPTNO"="B"."DEPTNO")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.24 0.23 0 1125 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.24 0.24 0 1125 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1125 pr=0 pw=0 time=236864 us)
140000 NESTED LOOPS (cr=1125 pr=0 pw=0 time=11201492 us)
4 SORT UNIQUE (cr=1 pr=0 pw=0 time=917 us)
4 INDEX FULL SCAN DEPT_X01 (cr=1 pr=0 pw=0 time=169 us)(object id 54057)
140000 INDEX FAST FULL SCAN EMP_DEPT_X01 (cr=1124 pr=0 pw=0 time=4912290 us)(object id 54055)
-- not exists 에 대한 처리는 nested loop anti join으로 처리됨
drop index dept_x01;
alter table dept add constraint dept_pk primary key (deptno);
explain plan for
select /* G */ /*+ leading(a) */ count(*)
from emp a
where not exists (select 'x'
from dept b
where a.deptno = b.deptno);
@cplan
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 223 (13)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | NESTED LOOPS ANTI | | 1 | 6 | 223 (13)|
| 3 | TABLE ACCESS FULL| EMP | 138K| 406K| 203 (4)|
|* 4 | INDEX UNIQUE SCAN| DEPT_PK | 4 | 12 | 0 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.15 0.15 0 872 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.15 0.15 0 872 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=872 pr=0 pw=0 time=153523 us)
0 NESTED LOOPS ANTI (cr=872 pr=0 pw=0 time=153190 us)
140000 TABLE ACCESS FULL EMP (cr=870 pr=0 pw=0 time=3500023 us)
3 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=939 us)(object id 54058) -- anti join에서도 캐시효과가 나타남
/** 9i test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.68 3.59 87 283 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.68 3.65 87 283 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=283 r=87 w=0 time=3597643 us)
0 NESTED LOOPS ANTI (cr=283 r=87 w=0 time=3597624 us)
140000 INDEX FAST FULL SCAN EMP_DEPT_X01 (cr=281 r=87 w=0 time=608307 us)(object id 40285)
140000 INDEX UNIQUE SCAN DEPT_PK (cr=2 r=0 w=0 time=1186621 us)(object id 40286) --9i에서는 anti join에서도 캐시효과 없음(단, buffer pinning 효과는 있음)
**/
-- no_unnest하게 되면 filter 방식으로 처리됨
explain plan for
select /* H */ /*+ leading(a) */ count(*)
from emp a
where not exists (select /*+ no_unnest */ 'x'
from dept b
where a.deptno = b.deptno);
@cplan
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 203 (4)|
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL| EMP | 138K| 406K| 203 (4)|
|* 4 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 3 | 0 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "B"
WHERE "B"."DEPTNO"=:B1))
4 - access("B"."DEPTNO"=:B1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.14 0.14 0 873 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.15 0.15 0 873 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=873 pr=0 pw=0 time=146675 us)
0 FILTER (cr=873 pr=0 pw=0 time=146655 us)
140000 TABLE ACCESS FULL EMP (cr=870 pr=0 pw=0 time=3639975 us)
3 INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=0 us)(object id 54058)
-- not in에 대한 처리는 index를 이용할 수 없으므로 table full scan을 이용하고 filter 방식으로 처리됨
explain plan for
select /* I */ /*+ leading(a) */ count(*)
from emp a
where a.deptno not in (select b.deptno
from dept b);
@cplan
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 206 (4)|
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL| EMP | 138K| 406K| 203 (4)|
|* 4 | INDEX FULL SCAN | DEPT_PK | 1 | 3 | 1 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DEPT" "B" WHERE
LNNVL("B"."DEPTNO"<>:B1)))
4 - filter(LNNVL("B"."DEPTNO"<>:B1))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.21 0.21 0 873 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.22 0.21 0 873 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=873 pr=0 pw=0 time=212535 us)
0 FILTER (cr=873 pr=0 pw=0 time=212490 us)
140000 TABLE ACCESS FULL EMP (cr=870 pr=0 pw=0 time=6859975 us)
3 INDEX FULL SCAN DEPT_PK (cr=3 pr=0 pw=0 time=0 us)(object id 54058)
댓글 없음:
댓글 쓰기