SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) LEADING(D@SUB) USE_NL(E@MAIN) */
e.employee_id, e.first_name, e.last_name, email, e.salary, e.commission_pct
FROM employee e
WHERE EXISTS (SELECT /*+ QB_NAME(SUB) */
NULL
FROM department d
WHERE d.location_id = 1800
AND d.department_id = e.department_id);
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 2 |00:00:00.01 | 6 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 2 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS | | 1 | 4 | 252 | 4 (25)| 00:00:01 | 2 |00:00:00.01 | 4 | | | |
| 3 | SORT UNIQUE | | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | | 0 (0)| | 2 |00:00:00.01 | 2 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 2 | 4 | 224 | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 2 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."LOCATION_ID"=1800)
6 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
-- 불필요한 sort unique 발생
-- Any Subquery To Normal Join 기법으로 서브쿼리를 Driving 집합으로 사용하면서도 추가적인 Sort Unique Operation이 발생하지 않음.
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) LEADING(D@SUB) USE_NL(E@MAIN) */
e.employee_id, e.first_name, e.last_name, e.email, e.salary, e.commission_pct
FROM employee e
WHERE e.department_id = ANY (SELECT /*+ QB_NAME(SUB) */ --> ANY로 변경
d.department_id
FROM department d
WHERE d.location_id = 1800);
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | | 2 |00:00:00.01 | 7 |
| 2 | NESTED LOOPS | | 1 | 4 | 2 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 2 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 2 | 4 | 2 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."LOCATION_ID"=1800)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
SELECT /*+ QB_NAME(MAIN) USE_NL(D@SUB E@MAIN) */
e.employee_id, e.first_name, e.last_name, e.email, e.salary, e.commission_pct
FROM employee e
WHERE e.department_id IN (SELECT /*+ QB_NAME(SUB) INDEX(D DEPT_LOCATION_IX) */
d.department_id
FROM department d
WHERE d.location_id = 1800);
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 4 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."LOCATION_ID"=1800)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
| 10053 Trace |
|---|
| {code:sql} CBQT: Considering cost-based transformation on query block MAIN (#0) ******************************** COST-BASED QUERY TRANSFORMATIONS ******************************** FPD: Considering simple filter push (pre rewrite) in query block MAIN (#0) FPD: Current where clause predicates "E"."DEPARTMENT_ID"=ANY (SELECT /*+ QB_NAME ("SUB") INDEX ("D" "DEPT_LOCATION_IX") */ "D"."DEPARTMENT_ID" FROM "DEPARTMENT" "D") ---> In 서브쿼리가 = Any 서브쿼리로 바뀌었다.(pre rewrite 과정) |
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block MAIN (#1) that are valid to unnest.
Subquery Unnesting on query block MAIN (#1)SU: Performing unnesting that does not require costing. --> Costing이 필요 없다.
SU: Considering subquery unnest on query block MAIN (#1).
SU: Checking validity of unnesting subquery SUB (#2)
SU: Passed validity checks.
SU: Transforming ANY subquery to a join.
Registered qb: SEL$526A7031 0x2bed0d1c (SUBQUERY UNNEST MAIN; SUB) --> 새로운 쿼리블럭 생성
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$526A7031 (#1) that are valid to merge.
kkqctdrvTD-cleanup: transform(in-use=6396, alloc=9348) :
call(in-use=828, alloc=16360), compile(in-use=115056, alloc=128964), execution(in-use=2132, alloc=4060)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$526A7031 (#1)
kkqctdrvTD-start: :
call(in-use=1124, alloc=16360), compile(in-use=107392, alloc=128964), execution(in-use=2132, alloc=4060)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=1124, alloc=16360), compile(in-use=107728, alloc=128964), execution(in-use=2132, alloc=4060)
kkqctdrvTD-end:
call(in-use=1124, alloc=16360), compile(in-use=107908, alloc=128964), execution(in-use=2132, alloc=4060)
JPPD: Applying transformation directives
query block MAIN transformed to SEL$526A7031 (#1)
FPD: Considering simple filter push in query block SEL$526A7031 (#1)
"E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1800
try to generate transitive predicate from check constraints for query block SEL$526A7031 (#1)
finally: "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1800
--> View Merging 작업시 인라인뷰를 해체하였으므로 메인 쿼리의 Where 절이 바뀌어야 하는데 오라클은 이 작업을 FPD로 나타내고 있다.
* 서브쿼리의 작성을 마치기 전에 충분히 고민하라
(Exists or In or Any or All : Sort Unique의 발생 유무)
h1. 2.18 CRSW (Correlated Removal Subquery Using Window Function) : 상관 서브쿼리를 사용할 때 분석함수를 사용할 수 있는 경우 서브쿼리를 제거하라.
* 10g에서 Query Transformation 관련된 신기능 중에 가장 눈에 띄는 것이 RSW 기능
* MAX(일련번호 혹은 일자)등을 구하여 거기에 해당하는 데이터를 조회하는 경우 MAX 함수를 이용한 서브쿼리의 경우
오라클은 RSW 기능을 이용하여 서브쿼리를 제거.
{code:sql}
SELECT a.employee_id, a.first_name, a.last_name, b.department_name
FROM employee a, department b
WHERE a.department_id = b.department_id
AND a.employee_id = (SELECT MAX (s.employee_id)
FROM employee s
WHERE s.department_id = b.department_id);
----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | VIEW | VW_WIF_1 | 106 | 7208 | 6 | 00:00:01 |
| 2 | WINDOW BUFFER | | 106 | 6466 | 6 | 00:00:01 |
| 3 | MERGE JOIN | | 106 | 6466 | 6 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 27 | 540 | 2 | 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 | 00:00:01 |
| 6 | SORT JOIN | | 107 | 4387 | 4 | 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEE | 107 | 4387 | 3 | 00:00:01 |
----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("VW_COL_5" IS NOT NULL) --> 엉뚱한 Filter가 생겼다.
6 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
6 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
-- 10053
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: Passed validity checks.
SU: Unnesting subquery query block SEL$2 (#2)Subquery elimination for query block SEL$2 (#2) --> 서브쿼리 Unnesing 하고 난 직 후 서브쿼리를 제거.
Registered qb: SEL$EAA77DC2 0x2c1a0d1c (QUERY BLOCK TABLES CHANGED SEL$1)
-- 11gR2
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: Passed validity checks.
SU: Unnesting subquery query block SEL$2 (#2)Subquery removal for query block SEL$2 (#2)
RSW: Valid for correlated where clause SQ removal SEL$2 (#2) -> RSW 라는 약어 사용.(Uncorrelated RSW 기능이 추가 되었음을 알 수 있다.)
Registered qb: SEL$EAA77DC2 0x6ca1ff70 (QUERY BLOCK TABLES CHANGED SEL$1)
-- 변형 SQL
SELECT ...
FROM (SELECT ...
, CASE A.EMPLOYEE_ID
WHER MAX(A.EMPLOYEE_ID) OVER (PARTITION BY A.DEPARTMENT_ID)
THEN A.ROWID
END VW_COL_5
FROM DEPARTMENT B, EMPLOYEE A
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) VW_WIF_1
WHERE VW_COL_5 IS NOT NULL
WITH v AS (SELECT /*+ inline */
department_id, SUM (salary) AS sal
FROM employee
WHERE job_id = 'ST_CLERK'
GROUP BY department_id )
SELECT d.department_id, d.department_name, v.sal
FROM department d, v
WHERE d.department_id = v.department_id
AND v.sal = (SELECT MAX (v.sal)
FROM v) ;
------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | MERGE JOIN | | 5 | 275 | 6 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 27 | 432 | 2 | 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 | 00:00:01 |
| 4 | SORT JOIN | | 5 | 195 | 4 | 00:00:01 |
| 5 | VIEW | | 5 | 195 | 3 | 00:00:01 |
| 6 | WINDOW BUFFER | | 5 | 80 | 3 | 00:00:01 |
| 7 | HASH GROUP BY | | 5 | 80 | 3 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 6 | 96 | 2 | 00:00:01 |
| 9 | INDEX RANGE SCAN | EMP_JOB_IX| 6 | | 1 | 00:00:01 |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
4 - filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
5 - filter("V"."SAL"="ITEM_0")
9 - access("JOB_ID"='ST_CLERK')
-- 10053
Subquery removal for query block SEL$3 (#0) --> 쿼리 블럭3에 RSW 기능이 수행
RSW: Valid for uncorrelated where clause SQ removal SEL$3 (#0)
RSW: query after subquery removal:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME","V"."SAL" "SAL" FROM "HR"."DEPARTMENT" "D", (SELECT "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID",SUM("EMPLOYEE"."SALARY") "SAL",MAX(SUM("EMPLOYEE"."SALARY")) OVER ( PARTITION BY ) "ITEM_0" FROM "HR"."EMPLOYEE" "EMPLOYEE" WHERE "EMPLOYEE"."JOB_ID"='ST_CLERK' GROUP BY "EMPLOYEE"."DEPARTMENT_ID") "V" WHERE "D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID" AND "V"."SAL"="V"."ITEM_0"
Subquery converted.
-- 변경된 쿼리
SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME","V"."SAL" "SAL"
FROM "HR"."DEPARTMENT" "D",
(SELECT "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID",SUM("EMPLOYEE"."SALARY") "SAL",
MAX(SUM("EMPLOYEE"."SALARY")) OVER ( PARTITION BY ) "ITEM_0"
FROM "HR"."EMPLOYEE" "EMPLOYEE"
WHERE "EMPLOYEE"."JOB_ID"='ST_CLERK' GROUP BY "EMPLOYEE"."DEPARTMENT_ID") "V"
WHERE "D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID" AND "V"."SAL"="V"."ITEM_0"
SELECT /*+ QB_NAME(MAIN) */
d.department_id, d.department_name, d.location_id
FROM department d
WHERE EXISTS (SELECT /*+ QB_NAME(SUB) */
NULL
FROM employee e
WHERE e.department_id = d.department_id)
AND d.location_id = 1700;
---------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | NESTED LOOPS SEMI | | 8 | 184 | 2 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 21 | 420 | 2 | 00:00:01 |
| 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 21 | | 1 | 00:00:01 |
| 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 41 | 123 | 0 | |
---------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("D"."LOCATION_ID"=1700)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
-- 10053
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block MAIN (#1) that are valid to unnest.
Subquery Unnesting on query block MAIN (#1)SU: Performing unnesting that does not require costing.
--> From 절에 테이블이 하나만 존재함으로 Semi Join으로 변환하는 과정에서 Costing이 필요 없다.
SU: Considering subquery unnest on query block MAIN (#1).
SU: Checking validity of unnesting subquery SUB (#2)
SU: Passed validity checks.
SU: Transforming EXISTS subquery to a join. <--
Registered qb: SEL$526A7031 0x2fcf1da8 (SUBQUERY UNNEST MAIN; SUB)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$526A7031 nbfros=2 flg=0
fro(0): flg=0 objn=71188 hint_alias="D"@"MAIN"
fro(1): flg=0 objn=71191 hint_alias="E"@"SUB"
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$526A7031 (#1) that are valid to merge.
kkqctdrvTD-cleanup: transform(in-use=6088, alloc=6340) :
call(in-use=800, alloc=16360), compile(in-use=105740, alloc=120308), execution(in-use=1924, alloc=4060)
kkqctdrvTD-end:
call(in-use=800, alloc=16360), compile(in-use=99444, alloc=120308), execution(in-use=1924, alloc=4060)
SU: Transforming EXISTS subquery to a join.
SJC: Considering set-join conversion in query block SEL$526A7031 (#1)
.....
FPD: Considering simple filter push in query block SEL$526A7031 (#1)
"D"."LOCATION_ID"=1700 AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$526A7031 (#1)
finally: "D"."LOCATION_ID"=1700 AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
kkoqbc: optimizing query block SEL$526A7031 (#1) --> Cost Estimator(kkoqbc)가 costing.
SELECT a.job_id, a.department_id
FROM employee a
WHERE NOT EXISTS (SELECT 1
FROM department d
WHERE d.department_id = a.department_id);
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | NESTED LOOPS ANTI | | 106 | 1696 | 3 | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEE | 107 | 1284 | 3 | 00:00:01 |
| 3 | INDEX UNIQUE SCAN | DEPT_ID_PK| 1 | 4 | 0 | |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("D"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")
-- 10053
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest. --> Anti Join으로 변환 가능하다.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing. --> 비용계산 필요 없다.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: Passed validity checks.
SU: Unnesting subquery query block SEL$2 (#2)SU: Transform ALL/NOTEXISTS subquery into a regular antijoin. --> Anti Join으로 변환
Registered qb: SEL$5DA710D3 0xa4e1e1c (SUBQUERY UNNEST SEL$1; SEL$2) --> 새로운 쿼리 블럭 생성
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
fro(0): flg=0 objn=71191 hint_alias="A"@"SEL$1"
fro(1): flg=0 objn=71188 hint_alias="D"@"SEL$2"
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$5DA710D3 (#1) that are valid to merge. --> SVM
kkqctdrvTD-cleanup: transform(in-use=5816, alloc=7420) :
call(in-use=732, alloc=16360), compile(in-use=104668, alloc=116200), execution(in-use=1856, alloc=4060)
kkqctdrvTD-end:
call(in-use=732, alloc=16360), compile(in-use=97232, alloc=116200), execution(in-use=1856, alloc=4060)
SU: Unnesting subquery query block SEL$2 (#2)SU: Transform ALL/NOTEXISTS subquery into a regular antijoin.
SJC: Considering set-join conversion in query block SEL$5DA710D3 (#1)
.....
query block SEL$1 transformed to SEL$5DA710D3 (#1) -->
FPD: Considering simple filter push in query block SEL$5DA710D3 (#1)
"D"."DEPARTMENT_ID"="A"."DEPARTMENT_ID" -->
try to generate transitive predicate from check constraints for query block SEL$5DA710D3 (#1)
finally: "D"."DEPARTMENT_ID"="A"."DEPARTMENT_ID"
kkoqbc: optimizing query block SEL$5DA710D3 (#1)
:
call(in-use=980, alloc=16360), compile(in-use=99604, alloc=116200), execution(in-use=1856, alloc=4060)
kkoqbc-subheap (create addr=0x0A57BFBC)
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
d.department_id, d.department_name, location_id
FROM department d
WHERE d.department_id NOT IN (SELECT /*+ QB_NAME(SUB) */
e.department_id
FROM employee e)
AND d.location_id = 1700;
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 | 2 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | 3 | 2 |
| 2 | NESTED LOOPS ANTI SNA | | 0 | 10 | 0 |00:00:00.01 | 0 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 0 | 21 | 0 |00:00:00.01 | 0 | 0 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 0 | 21 | 0 |00:00:00.01 | 0 | 0 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 0 | 56 | 0 |00:00:00.01 | 0 | 0 |
|* 6 | TABLE ACCESS FULL | EMPLOYEE | 1 | 1 | 1 |00:00:00.01 | 3 | 2 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
4 - access("D"."LOCATION_ID"=1700)
5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
6 - filter("E"."DEPARTMENT_ID" IS NULL)
-- null 허용 컬럼으로 인해 Anti Join 실행 불가 (10g 이하)
-- 데이터 출력도 불가 (Not In과 Not Exists 차이 이해 필요)
-- not null 조건 추가
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
d.department_id, d.department_name, location_id
FROM department d
WHERE d.department_id NOT IN (SELECT /*+ QB_NAME(SUB) */
e.department_id
FROM employee e
where e.department_id is not null)
AND d.location_id = 1700;
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 16 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS ANTI | | 1 | 10 | 16 |00:00:00.01 | 9 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 21 | 21 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 21 | 21 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 21 | 56 | 5 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."LOCATION_ID"=1700)
4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID" IS NOT NULL)
SELECT /*+ QB_NAME(MAIN) */
d.department_id, d.department_name, location_id
FROM department d
WHERE d.department_id NOT IN (SELECT /*+ QB_NAME(SUB) */
e.department_id
FROM employee e)
AND d.location_id = 1700;
----------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 5 | |
| 1 | FILTER | | | | | |
| 2 | NESTED LOOPS ANTI SNA | | 10 | 230 | 5 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 21 | 420 | 2 | 00:00:01 |
| 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 21 | | 1 | 00:00:01 |
| 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 56 | 168 | 0 | |
| 6 | TABLE ACCESS FULL | EMPLOYEE | 1 | 6 | 3 | 00:00:01 |
----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter( IS NULL)
4 - access("D"."LOCATION_ID"=1700)
5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
6 - filter("E"."DEPARTMENT_ID" IS NULL)
-- SNA : Single Null-Aware Anti Join의 약자
-- 조인되는 컬럼 중에 한쪽만 Null 허용 컬럼이라는 뜻
-- 양측의 컬럼이 모두 Null 허용 컬럼이라면 Nested Loops Anti NA로 나타난다.
-- 10053
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block MAIN (#1) that are valid to unnest.
Subquery Unnesting on query block MAIN (#1)SU: Performing unnesting that does not require costing. -->
SU: Considering subquery unnest on query block MAIN (#1).
SU: Checking validity of unnesting subquery SUB (#2)
SU: Passed validity checks.
SU: Transform ALL subquery to a null-aware antijoin.
--> Not In 서브쿼리가 먼저 NOT = ALL 서브쿼리로 바뀌었으며 다시 Null Aware Anti Join으로 변환
SU: Checking validity of unnesting subquery SUB (#3)
SU: Validity checks failed.
Registered qb: SEL$526A7031 0xc7edc70 (SUBQUERY UNNEST MAIN; SUB)
.....
try to generate transitive predicate from check constraints for query block SUB (#2)
finally: "E"."DEPARTMENT_ID" IS NULL
--> 서브쿼리 쪽의 조인 컬럼이 Null인 데이터를 먼저 찾아 Scan을 멈추기 위해 IS NULL 조건 추가.
try to generate transitive predicate from check constraints for query block SEL$526A7031 (#1)
finally: "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700 AND NOT EXISTS (SELECT /*+ QB_NAME ("SUB") */ 0 FROM "EMPLOYEE" "E")
--> Not In 서브쿼리가 Not exists로 변환
SELECT e.job_id, e.department_id
FROM employee e, department d
WHERE e.department_id = d.department_id(+)
AND d.department_id IS NULL;
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | NESTED LOOPS ANTI | | 106 | 1696 | 3 | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEE | 107 | 1284 | 3 | 00:00:01 |
| 3 | INDEX UNIQUE SCAN | DEPT_ID_PK| 1 | 4 | 0 | |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$6E71C6F6")
OUTER_JOIN_TO_INNER(@"SEL$1") -->
OUTLINE(@"SEL$1")
FULL(@"SEL$6E71C6F6" "E"@"SEL$1")
INDEX(@"SEL$6E71C6F6" "D"@"SEL$1" ("DEPARTMENT"."DEPARTMENT_ID"))
LEADING(@"SEL$6E71C6F6" "E"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$6E71C6F6" "D"@"SEL$1")
END_OUTLINE_DATA
*/
-- 오라클의 변형 쿼리
SELECT e.*
FROM employee e
WHERE NOT EXISTS (SELECT 1
FROM department d
WHERE d.department_id = e.department_id);
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 73 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 107 | 7383 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 27 | 108 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
-- 10053
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "E"."JOB_ID" "JOB_ID","E"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "TLO"."EMPLOYEE" "E","TLO"."DEPARTMENT" "D" WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+) AND "D"."DEPARTMENT_ID" IS NULL
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Considering outer-join elimination on query block SEL$1 (#0)
OJE: considering predicate"E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+)
rejected
OJE: considering predicate"D"."DEPARTMENT_ID" IS NULL --> IS NULL 조건에 의해서
OJE: Converting outer join of DEPARTMENT and EMPLOYEE to anti-join. --> Anti Join으로 변환
considered
Registered qb: SEL$6E71C6F6 0xcdaf3fc (OUTER-JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$6E71C6F6 nbfros=2 flg=0
fro(0): flg=0 objn=71188 hint_alias="D"@"SEL$1"
fro(1): flg=0 objn=71191 hint_alias="E"@"SEL$1"
OJE: outer-join eliminated
-- 먼저 FK 생성
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER SESSION SET "_always_semi_join" = CHOOSE;
-- _always_semi_join 파라미터를 Default 값인 Choose로 한다. Semi Join이 수행되어야만 JE가 발생.
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.salary
FROM employee e
WHERE EXISTS (SELECT 1
FROM department d
WHERE d.department_id = e.department_id)
AND e.job_id = 'SH_CLERK';
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 | 1040 | 2 | 00:00:01 |
| 2 | INDEX RANGE SCAN | EMP_JOB_IX| 20 | | 1 | 00:00:01 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)
2 - access("E"."JOB_ID"='SH_CLERK')
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$BB28CCCD")
ELIMINATE_JOIN(@"SEL$5DA710D3" "D"@"SEL$2") -->
OUTLINE(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$BB28CCCD" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))
END_OUTLINE_DATA
*/
-- EJE가 정상적으로 수행.
-- JE 기능이 수행되려면 중간 단계가 하나 더 필요하다. 서브쿼리를 조인으로 바꾸어야만 JE가 수행 될 수 있기 때문.
-- 10053
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: Passed validity checks.
SU: Transforming EXISTS subquery to a join. --> Exists 서브쿼리가 조인으로 바뀜
Registered qb: SEL$5DA710D3 0xd511e1c (SUBQUERY UNNEST SEL$1; SEL$2)
...
*************************
Join Elimination (JE)
*************************
JE: cfro: EMPLOYEE objn:71188 col#:11 dfro:DEPARTMENT dcol#:11 -->
Query block (07AE4924) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME","E"."EMAIL" "EMAIL","E"."SALARY" "SALARY" FROM "TLO"."DEPARTMENT" "D","TLO"."EMPLOYEE" "E" WHERE "E"."JOB_ID"='SH_CLERK' AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
JE: eliminate table: DEPARTMENT --> JE과정에서 DEPARTMENT 테이블 제거
Registered qb: SEL$BB28CCCD 0x7ae4924 (JOIN REMOVED FROM QUERY BLOCK SEL$5DA710D3; SEL$5DA710D3; "D"@"SEL$2")
-- 원본 SQL에서 Exists를 Not Exists로 바꾸어 Anti Join이 수행되는 경우에도 JE가 똑같이 발생