2.19_URSW_(Uncorrelated_Removal_Subquery_Using_Window_Function)
2011.10.09 07:37
-- Uncorrelated Subquery(비상관 서브쿼리) : 서브쿼리 내에 메인쿼리와의 조인절이 없다는 뜻이다.
-- 비상관 서브쿼리를 사용할때 분석함수를 사용할 수 있는경우는 서브쿼리를 제거한다.
-- 11gR2 에서 추가된 기능
DROP INDEX DEPT_NAME_IDX;
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
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) ;
ALTER SESSION SET EVENTS '10053 trace name context off';
-- 인라인뷰 V를 미리 정의해놓고 아래의 SELECT절에서 두번 사용한 것을 알 수 있다.
-- 최소한 EMPLOYEE 테이블에 대한 액세스가 두번은 나와야 한다.
------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | MERGE JOIN | | 3 | 165 | 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 | | 10 | 390 | 4 | 00:00:01 |
| 5 | VIEW | | 10 | 390 | 3 | 00:00:01 |
| 6 | WINDOW BUFFER | | 10 | 160 | 3 | 00:00:01 |
| 7 | HASH GROUP BY | | 10 | 160 | 3 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 | 320 | 2 | 00:00:01 |
| 9 | INDEX RANGE SCAN | EMP_JOB_IX| 20 | | 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')
-- 예상과 달리 EMPLOYEE 테이블에 대한 액세스가 한번나왔다.
-- USRW 기능은 중복 ACCESS를 방지한다.
**************************
Predicate Move-Around (PM)
**************************
PM: Passed validity checks.
PM: PM bypassed: checking.
query block SEL$2 (#0) unchanged
FPD: Considering simple filter push in query block SEL$2 (#0)
"D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID" AND "V"."SAL"="V"."ITEM_0"
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
finally: "D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID" AND "V"."SAL"="V"."ITEM_0"
JPPD: JPPD bypassed: View not on right-side of outer-join.
kkqfppRelFilter: Not pushing filter predicates in query block SEL$C500DBBE (#0) because no predicate to push
FPD: Considering simple filter push in query block SEL$C500DBBE (#0)
"EMPLOYEE"."JOB_ID"='ST_CLERK'
try to generate transitive predicate from check constraints for query block SEL$C500DBBE (#0)
finally: "EMPLOYEE"."JOB_ID"='ST_CLERK'
SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
"V"."SAL" "SAL"
FROM "TLO"."DEPARTMENT" "D", (
SELECT "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID",SUM("EMPLOYEE"."SALARY") "SAL",
MAX(SUM("EMPLOYEE"."SALARY")) OVER () "ITEM_0"
FROM "TLO"."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"
------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | MERGE JOIN | | 3 | 165 | 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 | | 10 | 390 | 4 | 00:00:01 |
| 5 | VIEW | | 10 | 390 | 3 | 00:00:01 |
| 6 | WINDOW BUFFER | | 10 | 160 | 3 | 00:00:01 |
| 7 | HASH GROUP BY | | 10 | 160 | 3 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 | 320 | 2 | 00:00:01 |
| 9 | INDEX RANGE SCAN | EMP_JOB_IX| 20 | | 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"="V"."ITEM_0")
9 - access("EMPLOYEE"."JOB_ID"='ST_CLERK')
-- 변환된 쿼리를 다시 트래이스한 결과 교재와 달리 SORT GROUP BY Operation이 사용되지않았다.
-- 개인적으로는 SORT GROUP BY Operation이 발생 안되는게 맞을꺼 같은데 교재에선 다른 환경 추가적으로 발생 된거 같음
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$C500DBBE (#0).
CSE: CSE not performed on query block SEL$2 (#0).
Subquery removal for query block SEL$3 (#0)
RSW: Valid for uncorrelated where clause SQ removal SEL$3 (#0)
--> 쿼리블럭 SEL$3에서 RSW기능이 수행
RSW: query after subquery removal:******* UNPARSED QUERY IS *******
--> 비상관 서브쿼리가 RSW기능에 의하여 제거
SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME","V"."SAL" "SAL"
FROM "TLO"."DEPARTMENT" "D", (
SELECT "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID",
SUM("EMPLOYEE"."SALARY") "SAL",
MAX(SUM("EMPLOYEE"."SALARY")) OVER ( PARTITION BY ) "ITEM_0"
FROM "TLO"."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.
SU: Considering subquery unnesting in query block SEL$2 (#0)
■ Control 파라미터를 이용한 제어(CRSW, URSW)
_remove_aggr_subquery = true
The Logical Optimizer (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 10월 9일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
