메뉴 건너뛰기

bysql.net


-- 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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^