-- 개념 : 단순 서브쿼리가 없어지고 조인으로 바뀌는 기능
-- 단순(Simple)  서브쿼리 : 서브쿼리 내부의 from절에 테이블이 하나만 존재해야한다
-- 복잡(Complex) 서브쿼리 : 서브쿼리에 Group by + 집합함수(min,max,avg)등을 사용
-- 제약조건
-- 1. 서브쿼리내의 FROM 절에 테이블 2개인 경우
-- 2. 서브쿼리내의 Group by + 집합함수(min,max,avg)등을 사용

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

SELECT /*+ 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)

ALTER SESSION SET EVENTS '10053 trace name context off';

-- 1. DEPARTMENT 테이블에 LOCATION_ID 인덱스가 있으므로 Driving 집합으로 하고
-- 2. NL조인으로 EMPLOYEE 테이블의 DEPARTMENT_ID 인덱스를 사용하는것이 최적의 조인방법
-- 3. 하지만 불필요한 Sort Unique가 발생 하였기 때문엔 건수가 많아질수록 부하는 기하급수적으로 증가

-----------------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name             | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                |                  |       |       |     4 |           |
| 1   |  NESTED LOOPS                   |                  |       |       |       |           |
| 2   |   NESTED LOOPS                  |                  |     4 |   252 |     4 |  00:00:01 |
| 3   |    SORT UNIQUE                  |                  |     1 |     7 |     2 |  00:00:01 |
| 4   |     TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |     1 |     7 |     2 |  00:00:01 |
| 5   |      INDEX RANGE SCAN           | DEPT_LOCATION_IX |     1 |       |     1 |  00:00:01 |
| 6   |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX|    10 |       |     0 |           |
| 7   |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE         |     4 |   224 |     1 |  00:00:01 |
-----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("D"."LOCATION_ID"=1800)
6 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

-- 어떻게 하면 Sort Unique Operation의 부하를 줄일 수 있을까?
-- SSU중의 대표격인 Any Subquery To Normal Join 기법 사용

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

SELECT /*+ 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) */
                                     D.DEPARTMENT_ID
                                FROM DEPARTMENT D
                               WHERE D.LOCATION_ID = 1800);

ALTER SESSION SET EVENTS '10053 trace name context off';

----------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name             | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |                  |       |       |     3 |           |
| 1   |  NESTED LOOPS                  |                  |       |       |       |           |
| 2   |   NESTED LOOPS                 |                  |     4 |   252 |     3 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |     1 |     7 |     2 |  00:00:01 |
| 4   |     INDEX RANGE SCAN           | DEPT_LOCATION_IX |     1 |       |     1 |  00:00:01 |
| 5   |    INDEX RANGE SCAN            | EMP_DEPARTMENT_IX|    10 |       |     0 |           |
| 6   |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |     4 |   224 |     1 |  00:00:01 |
----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."LOCATION_ID"=1800)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

-- Any Subquery To Normal Join의 과정(2장14절 OT : Any -> OR)
-- 1. 먼저 서브쿼리를 Any 서브쿼리로 바꾼다
-- 2. Any 서브쿼리를 인라인뷰로 바꾼다.
-- 3. View Merging 과정 수행
-- 4. Physical Optimizing 과정

-- IN 혹은 ANY를 이용하여 서브쿼리를 작성한다면 최적의 SQL이 될 수 있다.

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

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);   
                           
ALTER SESSION SET EVENTS '10053 trace name context off';


----------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name             | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |                  |       |       |     3 |           |
| 1   |  NESTED LOOPS                  |                  |       |       |       |           |
| 2   |   NESTED LOOPS                 |                  |     4 |   252 |     3 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |     1 |     7 |     2 |  00:00:01 |
| 4   |     INDEX RANGE SCAN           | DEPT_LOCATION_IX |     1 |       |     1 |  00:00:01 |
| 5   |    INDEX RANGE SCAN            | EMP_DEPARTMENT_IX|    10 |       |     0 |           |
| 6   |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |     4 |   224 |     1 |  00:00:01 |
----------------------------------------------------------+-----------------------------------+

-- 트레이스를 확인해보자

********************************

COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in query block MAIN (#0)
--> pre rewriterhkwjd : IN 서브커리가 ANY로 변환
FPD:  Current where clause predicates "E"."DEPARTMENT_ID" = ANY (SELECT /*+ QB_NAME ("SUB") INDEX ("D" "DEPT_LOCATION_IX") */ "D"."DEPARTMENT_ID" FROM "DEPARTMENT" "D")

*****************************
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이 필요치않음을 나타냄
--> 1. Simple 서브쿼리를 조인으로 바꾸는 작업은 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 0x1eefdfb0 (SUBQUERY UNNEST MAIN; SUB)
--> 새로운 쿼리블럭 생성
--> 2. SU 과정을 거치면 2개의 쿼리블럭이 조합되어 쿼리블럭이 새로 생성
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$526A7031 nbfros=2 flg=0
    fro(0): flg=0 objn=71194 hint_alias="E"@"MAIN"
    fro(1): flg=0 objn=71191 hint_alias="D"@"SUB"


*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$526A7031 (#1) that are valid to merge.
--> SU 과정을 마치고 나면 View Merging과정이 추가적으로 진행
kkqctdrvTD-cleanup: transform(in-use=8928, alloc=12232) :
    call(in-use=1576, alloc=16344), compile(in-use=134528, alloc=148056), execution(in-use=3464, alloc=4032)

kkqctdrvTD-end:
    call(in-use=1576, alloc=16344), compile(in-use=122432, alloc=148056), execution(in-use=3464, alloc=4032)

SU:   Transforming ANY subquery to a join.
--> 서브쿼리가 정상적인 조인으로 변환되었을을 알수 있다.
SJC: Considering set-join conversion in query block SEL$526A7031 (#1)


************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$526A7031 (#1)
kkqctdrvTD-start: :
    call(in-use=2128, alloc=16344), compile(in-use=125432, alloc=148056), execution(in-use=3464, alloc=4032)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=2128, alloc=16344), compile(in-use=126080, alloc=148056), execution(in-use=3464, alloc=4032)

kkqctdrvTD-end:
    call(in-use=2128, alloc=16344), compile(in-use=126456, alloc=148056), execution(in-use=3464, alloc=4032)

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
--> FPD : View Merging 작업시 인라인뷰를 해체하였으므로 메인 쿼리의 WHERE 절이 변환되는 작업 표시,
          View Merging 과정에서 일어나는 세부사항으로 생각해야 할것이다.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") USE_NL ("E") USE_NL ("D") INDEX ("D" "DEPT_LOCATION_IX") */
       "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME",
       "E"."EMAIL" "EMAIL","E"."SALARY" "SALARY","E"."COMMISSION_PCT" "COMMISSION_PCT"
FROM   "TLO"."DEPARTMENT" "D","TLO"."EMPLOYEE" "E"
WHERE  "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
AND    "D"."LOCATION_ID"=1800
--> SSU + View Merging + FPD 작업을 마친 쿼리
kkoqbc: optimizing query block SEL$526A7031 (#1)
       
        :
    call(in-use=2264, alloc=16344), compile(in-use=127736, alloc=148056), execution(in-use=3464, alloc=4032)

kkoqbc-subheap (create addr=0x000000001DE0C4A8)

----------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name             | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |                  |       |       |     3 |           |
| 1   |  NESTED LOOPS                  |                  |       |       |       |           |
| 2   |   NESTED LOOPS                 |                  |     4 |   252 |     3 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |     1 |     7 |     2 |  00:00:01 |
| 4   |     INDEX RANGE SCAN           | DEPT_LOCATION_IX |     1 |       |     1 |  00:00:01 |
| 5   |    INDEX RANGE SCAN            | EMP_DEPARTMENT_IX|    10 |       |     0 |           |
| 6   |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |     4 |   224 |     1 |  00:00:01 |
----------------------------------------------------------+-----------------------------------+

-- 결론 : 불필요한 Sort Unique를 줄이기 위해서 IN으로 작성할것인지 EXISTS로 작성할 것인지 아니라면

          ANY 혹은 ALL을 사용할것인가의 갈림길. 항상 실행계획을 보고 신중하게 생각해야 한다.

The Logical Optimizer  (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 10월 9일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^