2.18_CRSW_(Correlated_Removal_Subquery_Using_Window_Function)
2011.10.09 16:37
-- correlate 1. (밀접한) 연관성이 있다 2. 연관성을 보여주다
-- 10g 에서 Query Transformation 관련 신기능중에 가장 눈에 띄는 기능이다.
-- 전제 : MAX(일련번호 혹은 일자)등을 구하여 거기에 해당하는 데이터를 조회하는 경우
-- 결과 : MAX함수를 이용한 서브쿼리가 가장 많이 사용인데 RSW 기능으로 서브쿼리를 제거해버린다.
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
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);
ALTER SESSION SET EVENTS '10053 trace name context off';
----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | VIEW | VW_WIF_1 | 104 | 7072 | 6 | 00:00:01 |
| 2 | WINDOW BUFFER | | 104 | 6344 | 6 | 00:00:01 |
| 3 | MERGE JOIN | | 104 | 6344 | 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)
6 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
6 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
-- 서브쿼리에 해당하는 OPERATION이 사라지고 엉뚱한 FILTER가 생김 : 1 - filter("VW_COL_5" IS NOT NULL)
*****************************
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)
Registered qb: SEL$EAA77DC2 0x1a81cf30 (QUERY BLOCK TABLES CHANGED SEL$1)
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$A82B07F9 (#1)
JPPD: Checking validity of push-down from query block SEL$A82B07F9 (#1) to query block SEL$6A96FB79 (#2)
Check Basic Validity for Non-Union View for query block SEL$6A96FB79 (#2)
JPPD: JPPD bypassed: No valid join condition found.
JPPD: No valid views found to push predicate into.
kkqctdrvTD-cleanup: transform(in-use=10792, alloc=11656) :
call(in-use=3216, alloc=16344), compile(in-use=169472, alloc=183448), execution(in-use=3872, alloc=4032)
kkqctdrvTD-end:
call(in-use=3216, alloc=16344), compile(in-use=158312, alloc=183448), execution(in-use=3872, alloc=4032)
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$A82B07F9 (#1)
JPPD: No valid views found to push predicate into.
query block SEL$1 transformed to SEL$A82B07F9 (#1)
FPD: Considering simple filter push in query block SEL$A82B07F9 (#1)
"VW_WIF_1"."VW_COL_5" IS NOT NULL
try to generate transitive predicate from check constraints for query block SEL$A82B07F9 (#1)
finally: "VW_WIF_1"."VW_COL_5" IS NOT NULL
-->서브쿼리에 해당하는 OPERATION이 사라지고 엉뚱한 FILTER가 생김
kkqfppRelFilter: Not pushing filter predicates in query block SEL$6A96FB79 (#2) because no predicate to push
FPD: Considering simple filter push in query block SEL$6A96FB79 (#2)
"A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$6A96FB79 (#2)
finally: "A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID"
--> 서브쿼리를 Unnesting하고 난 직후에 서브쿼리를 제거
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_WIF_1"."ITEM_1" "EMPLOYEE_ID","VW_WIF_1"."ITEM_2" "FIRST_NAME","VW_WIF_1"."ITEM_3" "LAST_NAME",
"VW_WIF_1"."ITEM_4" "DEPARTMENT_NAME"
FROM (
SELECT "A"."EMPLOYEE_ID" "ITEM_1","A"."FIRST_NAME" "ITEM_2",
"A"."LAST_NAME" "ITEM_3","B"."DEPARTMENT_NAME" "ITEM_4",
CASE "A"."EMPLOYEE_ID"
WHEN MAX("A"."EMPLOYEE_ID") OVER ( PARTITION BY "A"."DEPARTMENT_ID")
--> 분석함수를 이용하여 부서별로 MAX("A"."EMPLOYEE_ID")를 사용
THEN "A".ROWID
--> 결과값이 EMPLOYEE_ID와 같으면 ROWID출력
END "VW_COL_5"
--> ROWID값이 NULL이 아니라면 EMPLOYEE_ID는 부서별로 MAX("A"."EMPLOYEE_ID")와 같음을 보장한다.
FROM "TLO"."DEPARTMENT" "B","TLO"."EMPLOYEE" "A"
WHERE "A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID"
) "VW_WIF_1"
WHERE "VW_WIF_1"."VW_COL_5" IS NOT NULL
--> 그러므로 MAX 함수를 이용하여 서브쿼리가 제거 될수있는것이다.
kkoqbc: optimizing query block SEL$6A96FB79 (#2)
:
call(in-use=3368, alloc=16344), compile(in-use=161208, alloc=183448), execution(in-use=4064, alloc=8088)
kkoqbc-subheap (create addr=0x000000001A1FC8F8)
-- 제약조건 : /*+ NO_UNEST */ 힌트 사용시 기능을 사용할 수 없다.
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEE A, DEPARTMENT B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND A.EMPLOYEE_ID = (SELECT /*+ NO_UNNEST */
MAX (S.EMPLOYEE_ID)
FROM EMPLOYEE S
WHERE B.DEPARTMENT_ID = S.DEPARTMENT_ID);
ALTER SESSION SET EVENTS '10053 trace name context off';
----------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 28 | |
| 1 | FILTER | | | | | |
| 2 | MERGE JOIN | | 104 | 4368 | 6 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 27 | 540 | 2 | 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 | 00:00:01 |
| 5 | SORT JOIN | | 107 | 2354 | 4 | 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEE | 107 | 2354 | 3 | 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 7 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 10 | 70 | 2 | 00:00:01 |
| 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 10 | | 1 | 00:00:01 |
----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("A"."EMPLOYEE_ID"=)
5 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
5 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
9 - access("S"."DEPARTMENT_ID"=:B1)
*****************************
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: SU bypassed: Not enabled by hint/parameter.--> hint/parameter로 인한 Subquery Unnesting이 발생되지않았다.
SU: SU bypassed: Failed basic validity checks.
SU: Validity checks failed.
-- 제어 파라미터
_remove_aggr_subquery = true;
_unnest_subquery = true; -- 교재 오타
*****************************
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)
--> 11.1.0.7 없던 RSW 약어가 사용되었으며 해당 SQL을 Correlated RSW로 분류 하고 있다.
--> 11.2 Uncorrelated RSW 기능이 추가 (다음장 설명)
Registered qb: SEL$EAA77DC2 0x6ca1ff70 (QUERY BLOCK TABLES CHANGED SEL$1)
The Logical Optimizer (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 10월 9일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^