-- Semi Join 개념을 이야기할때 빠질 수 없는 3가지
1. 서브쿼리를 조인으로 바꾼다는 것
2. 조인으로 바뀐 서브쿼리쪽의 집합은 항상 후행처리가 된다는것
3. 조인으로 바뀐 서브쿼리의 부분을 Scan 할 시에 만족되는 첫 번째 Row만 발견하면 빠져 나온다는 것
-- 메인쿼리의 테이터를 서브쿼리로 체크하는 개념으로 Semi Join이 적용된 SQL에서는 서브쿼리 블록은

   항상 후행처리 될 수 밖에 없다.

■ 주의사항
-- 예외적으로 HASH JOIN RIGHT(SEMI/ANTI)인 경우와 DSJ(DRIVING SEMI JOIN)는 서브쿼리가 DRIVING 집합으로 처리된다.
   (2.28, 2.29장에서 다시 설명)
  

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;
--> DEPARTMENT 테이블에서 LOCATION_ID = 1700 인 데이타에 대하여
    EMPLOYEE 테이블에 DEPARTMENT_ID로 조인하여 존재하는 건을 RETURN 하는 SQL
  
---------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name             | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                  |       |       |     2 |           |
| 1   |  NESTED LOOPS SEMI            |                  |     8 |   176 |     2 |  00:00:01 | -- ①
| 2   |   TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |    21 |   399 |     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")

-- ① 서브쿼리가 조인으로 바뀐것
-- ② DEPT_LOCATION_IX 인덱스를 이용하여 DEPARTMENT SCAN
-- ③ 후행처리로 EMPLOYEE.EMP_DEPARTMENT_IX

*****************************
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절에 테이블이 하나만 존재함으로 COSTING이 필요 없다는것
--> 즉, COMPLEX 서브쿼리가 아니므로 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.
--> SU 과정을 거친 SQL이 완전한건 아니라 완벽한 테이블간 조인으로 바꿀라면 인라인뷰를 해체해 작업
    VIEW MERGING 과정이 필요하다.
Registered qb: SEL$526A7031 0x4bf068 (SUBQUERY UNNEST MAIN; SUB)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$526A7031 nbfros=2 flg=0
    fro(0): flg=0 objn=86726 hint_alias="D"@"MAIN"
    fro(1): flg=0 objn=86728 hint_alias="E"@"SUB"

-- 아래의 쿼리는 서브쿼리가 인라인뷰로 변환되는 과정은 마쳤지만 VIEW MERGING이 필요한 SQL

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';                
                 
SELECT /*+ QB_NAME(MAIN) */
       D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
  FROM DEPARTMENT D,
       (SELECT /*+ QB_NAME(SUB) NO_MERGE */
               E.DEPARTMENT_ID
          FROM EMPLOYEE E) E
 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
   AND LOCATION_ID = 1700;
  
ALTER SESSION SET EVENTS '10053 trace name context off';

--------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name            | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                 |       |       |     7 |           |
| 1   |  HASH JOIN                    |                 |    81 |  2592 |     7 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID | DEPARTMENT      |    21 |   399 |     2 |  00:00:01 |
| 3   |    INDEX RANGE SCAN           | DEPT_LOCATION_IX|    21 |       |     1 |  00:00:01 |
| 4   |   VIEW                        |                 |   107 |  1391 |     4 |  00:00:01 | -- ①
| 5   |    TABLE ACCESS FULL          | EMPLOYEE        |   107 |   321 |     4 |  00:00:01 |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("LOCATION_ID"=1700)

-- ① 아이디 4번에서 VIEW 존재
-- 위의 쿼리의 경우 VIEW MERGING 과정과 FPD 과정을 거쳐야 SQL이 완벽해진다.

*******************************
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=6484, alloc=7016) :
    call(in-use=11148, alloc=16360), compile(in-use=113728, alloc=124636), execution(in-use=2020, alloc=4060)

kkqctdrvTD-end:
    call(in-use=11148, alloc=16360), compile(in-use=106776, alloc=124636), execution(in-use=2020, alloc=4060)

SU:   Transforming EXISTS subquery to a join.
SJC: Considering set-join conversion in query block SEL$526A7031 (#1)

********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in query block SUB (#0)
FPD:  Current where clause predicates "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

FPD: Considering simple filter push (pre rewrite) in query block MAIN (#0)
FPD:  Current where clause predicates  EXISTS (SELECT /*+ QB_NAME ("SUB") */ NULL FROM "EMPLOYEE" "E") AND "D"."LOCATION_ID"=1700

OBYE:   Considering Order-by Elimination from view MAIN (#0)

************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$526A7031 (#1)
kkqctdrvTD-start: :
    call(in-use=11388, alloc=32736), compile(in-use=108904, alloc=124636), execution(in-use=2040, alloc=4060)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=11388, alloc=32736), compile(in-use=109220, alloc=124636), execution(in-use=2040, alloc=4060)

kkqctdrvTD-end:
    call(in-use=11388, alloc=32736), compile(in-use=109380, alloc=124636), execution(in-use=2040, 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)
"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"
--> FPD 과정에서 위의 조건이 통합된 쿼리블럭에 추가되었다.

Final query after transformations:
kkoqbc: optimizing query block SEL$526A7031 (#1)
--> COST ESTIMATEOR(kkoqbc)가 COSTING 하는 것을 볼수 있다.

-- 최종적으로 아래와 같이 완전한 SQL이 되었다

SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
  FROM DEPARTMENT D, EMPLOYEE E --> VIEW MERGING이 발생하여 인라인뷰가 제거됨
 WHERE D.DEPARTMENT_ID(SEMI) = E.DEPARTMENT_ID
   AND LOCATION_ID = 1700; --> FPD가 발생하여 WHERE 절이 추가 되었음

-- 실제로는 이렇게 사용할수없고 오라클이 내부적으로 사용한 로직이다.

-- 위의 트레이스에서 SEMI JOIN 변환과정이 수행될 때 여러단계를 거치게 되는데 최소한

   아래 4단계는 진행된다고 볼 수 있다.
1. 서브쿼리를 제거하고 인라인뷰를 생성하는 과정
2. VIEW MERGING
3. FPD 과정
4. PHYSICAL OPTIMIZING 과정(kkoqbc: optimizing query block SEL$526A7031 (#1))

-- SEMI/ANTI JOIN 의 강점은 필요에 따라 NL/SORT MERGE/HASH JOIN을 선택할 수 있다

EXPLAIN PLAN FOR                        
SELECT /*+ QB_NAME(MAIN) USE_HASH(D@MAIN E@SUB) */
       D.DEPARTMENT_ID, D.DEPARTMENT_NAME, LOCATION_ID
  FROM DEPARTMENT D
 WHERE EXISTS (SELECT /*+ QB_NAME(SUB) */
                      NULL
                 FROM EMPLOYEE E
                WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
   AND LOCATION_ID = 1700;
                              
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     8 |   176 |     4  (25)| 00:00:01 |
|*  1 |  HASH JOIN SEMI              |                   |     8 |   176 |     4  (25)| 00:00:01 | -- ①
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |    21 |   399 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX  |    21 |       |     1   (0)| 00:00:01 |
|   4 |   INDEX FULL SCAN            | EMP_DEPARTMENT_IX |   107 |   321 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - access("LOCATION_ID"=1700)

-- ① HASH JOIN

EXPLAIN PLAN FOR                        
SELECT /*+ QB_NAME(MAIN) USE_NL(D@MAIN E@SUB) */
       D.DEPARTMENT_ID, D.DEPARTMENT_NAME, LOCATION_ID
  FROM DEPARTMENT D
 WHERE EXISTS (SELECT /*+ QB_NAME(SUB) */
                      NULL
                 FROM EMPLOYEE E
                WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
   AND LOCATION_ID = 1700;                              
                              
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     8 |   176 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                   |     8 |   176 |     2   (0)| 00:00:01 | -- ①
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |    21 |   399 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX  |    21 |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("LOCATION_ID"=1700)
   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

-- ① NL JOIN

-- SEMI JOIN을 이용하지않고 FILTER 쿼리를 사용하려면 아래처럼 NO_UNNEST 힌트를 사용

EXPLAIN PLAN FOR                        
SELECT /*+ QB_NAME(MAIN) NO_UNNEST(@SUB) */
       D.DEPARTMENT_ID, D.DEPARTMENT_NAME, LOCATION_ID
  FROM DEPARTMENT D
 WHERE EXISTS (SELECT /*+ QB_NAME(SUB) */
                      NULL
                 FROM EMPLOYEE E
                WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
   AND LOCATION_ID = 1700;                              
                              
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    19 |    13   (0)| 00:00:01 |
|*  1 |  FILTER                      |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |    21 |   399 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX  |    21 |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |     2 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SUB") */ 0 FROM "EMPLOYEE" "E"
              WHERE "E"."DEPARTMENT_ID"=:B1))
   3 - access("LOCATION_ID"=1700)
   4 - access("E"."DEPARTMENT_ID"=:B1)

■ Control 파라미터를 이용한 제어

_always_semi_join = choose -- OFF로 변경시 SEMI JOIN을 수행할 수 없다.(조인방법별 선택 가능)
_unnest_subquery  = true -- SEMI JOIN과 하등관계는 없다

-- SUBQUERY FLATTENING : 서브쿼리가 조인으로 바뀌는것이고 두가지로 나눌수 있다.
-- 1. SUBQUERY UNNESTING : DRIVING 집합이 되며 필요한 경우 메인쿼리의 집합을 보존 시키기 위하여
                           DRIVING 집합에 SORT/HASH UNIQUE 를 발생시킨다.
-- 2. SEMI/ANTI          : 메인쿼리의 결과를 서브쿼리에서 체크하는 개념이므로 후행 집합이 되며
                           SORT/HASH UNIQUE가 발생되지 않는다.

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