7. OR-Expansion

조회 수 2804 추천 수 0 2011.05.31 13:16:38
멋진넘 *.18.66.42

7.OR-Expansion

 

(1)OR-Expansion 기본
▶ OR-Expansion의 개념은 Where 절에서 OR 조건이나 IN 조건을 마나면 Union All로 변형되는 경우를 말한다.
  같은 컬럼에 대하여 OR 조건이 발생한다면 대부분의 경우 Inlist Iterator Plan으로 처리된다.
  하지만 OR로 분기되는 컬럼(조건)이 다른 경우는 Inlist Iterator가 발생할 수 없고 Union All로만
  변환이 가능하다.

▶ TEST 환경 생성(ch4_19.txt 변형)
  CREATE TABLE YHN_EMP AS SELECT * FROM SCOTT.EMP;
  CREATE INDEX EMP_DEPTNO_IDX ON YHN_EMP(DEPTNO);
  CREATE INDEX EMP_JOB_IDX    ON YHN_EMP(JOB);
 
  SELECT * FROM YHN_EMP
   WHERE JOB    = 'CLERK'
      OR DEPTNO = 20;
 
  ---------------------------------------------------------------------------------------------------
  | Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                 |                |     7 |   609 |     2   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID     | YHN_EMP        |     7 |   609 |     2   (0)| 00:00:01 |
  |   2 |   BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          |
  |   3 |    BITMAP OR                     |                |       |       |            |          |
  |   4 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
  |*  5 |      INDEX RANGE SCAN            | EMP_JOB_IDX    |       |       |     1   (0)| 00:00:01 |
  |   6 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
  |*  7 |      INDEX RANGE SCAN            | EMP_DEPTNO_IDX |       |       |     1   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     5 - access("JOB"='CLERK')
     7 - access("DEPTNO"=20)

 

▶ OR 대신 AND 사용시...(참고)
  SELECT * FROM YHN_EMP
   WHERE JOB    = 'CLERK'
     AND DEPTNO = 20;
 
  ---------------------------------------------------------------------------------------------------
  | Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                 |                |     2 |   174 |     2   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID     | YHN_EMP        |     2 |   174 |     2   (0)| 00:00:01 |
  |   2 |   BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          |
  |   3 |    BITMAP AND                    |                |       |       |            |          |
  |   4 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
  |*  5 |      INDEX RANGE SCAN            | EMP_DEPTNO_IDX |       |       |     1   (0)| 00:00:01 |
  |   6 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
  |*  7 |      INDEX RANGE SCAN            | EMP_JOB_IDX    |       |       |     1   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     5 - access("DEPTNO"=20)
     7 - access("JOB"='CLERK')

  ALTER SESSION SET "_B_TREE_BITMAP_PLANS" = FALSE;
  SELECT * FROM YHN_EMP
   WHERE JOB    = 'CLERK'
      OR DEPTNO = 20;
 
  -----------------------------------------------------------------------------
  | Id  | Operation   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |     |   7 | 609 |   3   (0)| 00:00:01 |
  |*  1 |  TABLE ACCESS FULL| YHN_EMP |   7 | 609 |   3   (0)| 00:00:01 |
  -----------------------------------------------------------------------------
 
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     1 - filter("JOB"='CLERK' OR "DEPTNO"=20)

 

▶ 만약 JOB과 DEPTNO에 각각 생성된 인덱스를 사용하고 싶다면 아래와 같이 UNION ALL 형태로 바꿔야 한다.
  SELECT * FROM YHN_EMP
   WHERE JOB = 'CLERK'
  UNION ALL
  SELECT * FROM YHN_EMP
   WHERE DEPTNO = 20
     AND LNNVL(JOB = 'CLERK');
 
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                |     7 |   609 |     4  (50)| 00:00:01 |
  |   1 |  UNION-ALL                   |                |       |       |            |          |
  |   2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     4 |   348 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     4 |       |     1   (0)| 00:00:01 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     3 |   261 |     2   (0)| 00:00:01 |
  |*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     3 - access("JOB"='CLERK')
     4 - filter(LNNVL("JOB"='CLERK'))
     5 - access("DEPTNO"=20)

▶ JOB과 DEPTNO 컬럼을 선두로 갖는 두 인덱스가 각각 사용됨
  분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, YHN_EMP 테이블 액세스가 두번 발생함
  따라서 중복 액세스되는 영역(DEPTNO = 20 이면서 JOB = 'CLERK')의 데이터 비중이 작을 수록 효과적
  그 반대의 경우라면 오히려 비용이 증가되어 비효율 적임
  또한, UNION ALL의 중복을 없게 하려고 UNION ALL의 아래쪽에 오라클이 내부적으로 LNNVL 함수를 사용

 

▶ LNNVL 함수
  인수 값이 FALSE 이거나 UNKNOWN(알 수 없는 eg. NULL) 값일 때 TRUE 반환, TRUE 일 때는 FALSE 반환

 

▶ OR-Expansion의 제어 힌트
  USE_CONCAT : OR-Expansion을 유도하고자 할 때 사용
  NO_EXPAND  : OR-Expansion의 기능을 방지하고자 할 때 사용

  SELECT /*+ USE_CONCAT */ *
    FROM YHN_EMP
   WHERE JOB    = 'CLERK'
      OR DEPTNO = 20;
 
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                |     7 |   609 |     4   (0)| 00:00:01 |
  |   1 |  CONCATENATION               |                |       |       |            |          |
  |   2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     5 |   435 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     1 |       |     1   (0)| 00:00:01 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     2 |   174 |     2   (0)| 00:00:01 |
  |*  5 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     1 |       |     1   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     3 - access("DEPTNO"=20)
     4 - filter(LNNVL("DEPTNO"=20))
     5 - access("JOB"='CLERK')

 

(2)OR-Expansion 브랜치별 조인 순서 최적화
▶ TEST 환경 생성(ch4_20.txt 변형)
  DROP   TABLE YHN_DEPT;
  DROP   TABLE YHN_EMP ;
  CREATE TABLE YHN_DEPT AS SELECT * FROM SCOTT.DEPT;
  CREATE TABLE YHN_EMP  AS SELECT * FROM SCOTT.EMP;
 
  ALTER TABLE YHN_DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);
  CREATE INDEX EMP_SAL_IDX    ON YHN_EMP(SAL);
 
  SELECT /*+ NO_EXPAND */ *
    FROM YHN_EMP E, YHN_DEPT D
   WHERE D.DEPTNO  = E.DEPTNO
     AND E.SAL    >= 2000
     AND (E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO');
 
  ----------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |             |     2 |   116 |     5  (20)| 00:00:01 |
  |   1 |  MERGE JOIN                    |             |     2 |   116 |     5  (20)| 00:00:01 |
  |   2 |   TABLE ACCESS BY INDEX ROWID  | YHN_DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
  |   3 |    INDEX FULL SCAN             | DEPT_PK     |     4 |       |     1   (0)| 00:00:01 |
  |*  4 |   FILTER                       |             |       |       |            |          |
  |*  5 |    SORT JOIN                   |             |     6 |   228 |     3  (34)| 00:00:01 |
  |   6 |     TABLE ACCESS BY INDEX ROWID| YHN_EMP     |     6 |   228 |     2   (0)| 00:00:01 |
  |*  7 |      INDEX RANGE SCAN          | EMP_SAL_IDX |     6 |       |     1   (0)| 00:00:01 |
  ----------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     4 - filter("E"."JOB"='SALESMAN' OR "D"."LOC"='CHICAGO')
     5 - access("D"."DEPTNO"="E"."DEPTNO")
         filter("D"."DEPTNO"="E"."DEPTNO")
     7 - access("E"."SAL">=2000)

 

▶ 책 내용과 PLAN이 다름
  SELECT /*+ ORDERED USE_NL(E, D) */ *
    FROM YHN_EMP E, YHN_DEPT D
   WHERE D.DEPTNO  = E.DEPTNO
     AND E.SAL    >= 2000
     AND (E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO');
 
  ---------------------------------------------------------------------------------------------
  | Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT              |             |     2 |   116 |     8   (0)| 00:00:01 |
  |   1 |  NESTED LOOPS                 |             |       |       |            |          |
  |   2 |   NESTED LOOPS                |             |     2 |   116 |     8   (0)| 00:00:01 |
  |   3 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP     |     6 |   228 |     2   (0)| 00:00:01 |
  |*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     6 |       |     1   (0)| 00:00:01 |
  |*  5 |    INDEX UNIQUE SCAN          | DEPT_PK     |     1 |       |     0   (0)| 00:00:01 |
  |*  6 |   TABLE ACCESS BY INDEX ROWID | YHN_DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     4 - access("E"."SAL">=2000)
     5 - access("D"."DEPTNO"="E"."DEPTNO")
     6 - filter("E"."JOB"='SALESMAN' OR "D"."LOC"='CHICAGO')

 

▶ 책 내용과는 조금 다르지만.. NO_EXPAND를 이용해 OR-Expansion 하지 못하도록 막았다고 가정하자.
  SAL >= 2000 조건으로 EMP 테이블을 먼저 읽어 조인한 후 DEPT 테이블을 ACCESS하는 단계에서
  (E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO') 조건이 필터링으로 이루어지고 있다.
  만약, 드라이빙 조건(SAL >= 2000)의 변별력이 나빠 조인 액세스 건수가 많고, 필터되는 OR로
  묶인 두 조건의 변별력이 좋다면 위 실행계획은 비효율적임

 

▶ TEST 환경 생성(ch4_20.txt 변형)
  EMP  테이블에 JOB, DEPTNO - 각각 인덱스 생성
  DEPT 테이블에 LOC         - 추가 인덱스 생성

  DROP   INDEX EMP_JOB_IDX;
  DROP   INDEX EMP_DEPTNO_IDX;
  DROP   INDEX DEPT_LOC_IDX;
  CREATE INDEX EMP_JOB_IDX    ON YHN_EMP(JOB);
  CREATE INDEX EMP_DEPTNO_IDX ON YHN_EMP(DEPTNO);
  CREATE INDEX DEPT_LOC_IDX   ON YHN_DEPT(LOC);
  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'YHN_EMP' );
  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'YHN_DEPT');
 
  SELECT /*+ USE_CONCAT */ *
    FROM YHN_EMP E, YHN_DEPT D
   WHERE D.DEPTNO  = E.DEPTNO
     AND E.SAL    >= 2000
     AND (E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO');
 
  -------------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |                |     3 |   174 |     6   (0)| 00:00:01 |
  |   1 |  CONCATENATION                 |                |       |       |            |          |
  |   2 |   NESTED LOOPS                 |                |       |       |            |          |
  |   3 |    NESTED LOOPS                |                |     2 |   116 |     3   (0)| 00:00:01 |
  |   4 |     TABLE ACCESS BY INDEX ROWID| YHN_DEPT       |     1 |    20 |     2   (0)| 00:00:01 |
  |*  5 |      INDEX RANGE SCAN          | DEPT_LOC_IDX   |     1 |       |     1   (0)| 00:00:01 |
  |*  6 |     INDEX RANGE SCAN           | EMP_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
  |*  7 |    TABLE ACCESS BY INDEX ROWID | YHN_EMP        |     2 |    76 |     1   (0)| 00:00:01 |
  |   8 |   NESTED LOOPS                 |                |       |       |            |          |
  |   9 |    NESTED LOOPS                |                |     1 |    58 |     3   (0)| 00:00:01 |
  |* 10 |     TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |* 11 |      INDEX RANGE SCAN          | EMP_JOB_IDX    |     4 |       |     1   (0)| 00:00:01 |
  |* 12 |     INDEX UNIQUE SCAN          | DEPT_PK        |     1 |       |     0   (0)| 00:00:01 |
  |* 13 |    TABLE ACCESS BY INDEX ROWID | YHN_DEPT       |     1 |    20 |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     5 - access("D"."LOC"='CHICAGO')
     6 - access("D"."DEPTNO"="E"."DEPTNO")
     7 - filter("E"."SAL">=2000)
    10 - filter("E"."SAL">=2000)
    11 - access("E"."JOB"='SALESMAN')
    12 - access("D"."DEPTNO"="E"."DEPTNO")
    13 - filter(LNNVL("D"."LOC"='CHICAGO'))

 

▶ 각각 실행됨. 또한 두 쿼리의 교집합이 두 번 출력되는 것을 방지하려고 LNNVL 함수 사용됨

 

(3)같은 컬럼에 대한 OR-Expansion
▶ 아래의 두 쿼리는 100% 동일한 쿼리임
  SELECT * FROM YHN_EMP
   WHERE (DEPTNO = 10 OR DEPTNO = 30)
     AND ENAME = :ENAME;
 
  SELECT * FROM YHN_EMP
   WHERE DEPTNO IN (10, 30)
     AND ENAME = :ENAME;
 
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                |     1 |    38 |     2   (0)| 00:00:01 |
  |   1 |  INLIST ITERATOR             |                |       |       |            |          |
  |*  2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     9 |       |     1   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------

 

  따라서, 아래와 같이 OR-Expansion 처리가 가능함

  SELECT * FROM YHN_EMP
   WHERE DEPTNO = 30
     AND ENAME = :ENAME
  UNION ALL
  SELECT * FROM YHN_EMP    
   WHERE DEPTNO = 10
     AND ENAME = :ENAME ;
 
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                |     2 |    76 |     4  (50)| 00:00:01 |
  |   1 |  UNION-ALL                   |                |       |       |            |          |
  |*  2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     6 |       |     1   (0)| 00:00:01 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     3 |       |     1   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter("ENAME"=:ENAME)
     3 - access("DEPTNO"=30)
     4 - filter("ENAME"=:ENAME)
     5 - access("DEPTNO"=10)
 
▶ 9i까지는 위와 같은 형태 즉, 같은 컬럼에 대해서 OR 조건이나 IN-List도 OR-Expansion이 작동할 수 있었지만
  10g부터는 기본적으로 아래와 같이 IN-List Iterator 방식으로만 처리됨
  억지로 OR-Expansion을 유도하려면 USE_CONCAT 힌트에 인자를 제공하면 되지만 IN-List에 비해 나은 점이
  없으므로 굳이 그렇게 할 이유가 없다.

 

  SELECT /*+ QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */* FROM YHN_EMP
   WHERE (DEPTNO = 10 OR DEPTNO = 30)
     AND ENAME = :ENAME;
 
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                |     2 |    76 |     4   (0)| 00:00:01 |
  |   1 |  CONCATENATION               |                |       |       |            |          |
  |*  2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     3 |       |     1   (0)| 00:00:01 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     6 |       |     1   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------

 

▶ USE_CONCAT 힌트의 인자 값(참고)
  Outline Data 블럭을 보면 USE_CONCAT 힌트의 인자에 쿼리블럭명과 숫자가 사용됨
  2가지 패턴이 있음
  ① 8 : 분기 조건단위로 UNION ALL로 분리하라는 의미. DEFAULT
  ② 1 : UNION ALL로 분리가 가능하다면 최대한 분리하라는 의미임

  SELECT /*+ QB_NAME(MAIN) USE_CONCAT */ *
    FROM YHN_EMP E, YHN_DEPT D
   WHERE D.DEPTNO  = E.DEPTNO
     AND (E.JOB IN ('SALESMAN', 'CLERK') OR D.LOC IN ('CHICAGO', 'DALLAS'));
 
  --------------------------------------------------------------------------------------------------
  | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                |                |    15 |   870 |     9  (12)| 00:00:01 |
  |   1 |  CONCATENATION                  |                |       |       |            |          |
  |   2 |   NESTED LOOPS                  |                |       |       |            |          |
  |   3 |    NESTED LOOPS                 |                |     9 |   522 |     4   (0)| 00:00:01 |
  |   4 |     INLIST ITERATOR             |                |       |       |            |          |
  |   5 |      TABLE ACCESS BY INDEX ROWID| YHN_DEPT       |     2 |    40 |     2   (0)| 00:00:01 |
  |*  6 |       INDEX RANGE SCAN          | DEPT_LOC_IDX   |     2 |       |     1   (0)| 00:00:01 |
  |*  7 |     INDEX RANGE SCAN            | EMP_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
  |   8 |    TABLE ACCESS BY INDEX ROWID  | YHN_EMP        |     5 |   190 |     1   (0)| 00:00:01 |
  |   9 |   MERGE JOIN                    |                |     6 |   348 |     5  (20)| 00:00:01 |
  |* 10 |    TABLE ACCESS BY INDEX ROWID  | YHN_DEPT       |     2 |    40 |     2   (0)| 00:00:01 |
  |  11 |     INDEX FULL SCAN             | DEPT_PK        |     4 |       |     1   (0)| 00:00:01 |
  |* 12 |    SORT JOIN                    |                |     8 |   304 |     3  (34)| 00:00:01 |
  |  13 |     INLIST ITERATOR             |                |       |       |            |          |
  |  14 |      TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     8 |   304 |     2   (0)| 00:00:01 |
  |* 15 |       INDEX RANGE SCAN          | EMP_JOB_IDX    |     8 |       |     1   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------------
  
  Outline Data
  -------------
    /*+
        (중간생략...)
        USE_CONCAT(@"MAIN" 8 OR_PREDICATES(2))
        (중간생략...)
    */
  
  SELECT /*+ QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */ *
    FROM YHN_EMP E, YHN_DEPT D
   WHERE D.DEPTNO  = E.DEPTNO
     AND (E.JOB IN ('SALESMAN', 'CLERK') OR D.LOC IN ('CHICAGO', 'DALLAS'));
 
  -------------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |                |    14 |   812 |    16  (13)| 00:00:01 |
  |   1 |  CONCATENATION                 |                |       |       |            |          |
  |   2 |   NESTED LOOPS                 |                |       |       |            |          |
  |   3 |    NESTED LOOPS                |                |     5 |   290 |     3   (0)| 00:00:01 |
  |   4 |     TABLE ACCESS BY INDEX ROWID| YHN_DEPT       |     1 |    20 |     2   (0)| 00:00:01 |
  |*  5 |      INDEX RANGE SCAN          | DEPT_LOC_IDX   |     1 |       |     1   (0)| 00:00:01 |
  |*  6 |     INDEX RANGE SCAN           | EMP_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
  |   7 |    TABLE ACCESS BY INDEX ROWID | YHN_EMP        |     5 |   190 |     1   (0)| 00:00:01 |
  |   8 |   NESTED LOOPS                 |                |       |       |            |          |
  |   9 |    NESTED LOOPS                |                |     4 |   232 |     3   (0)| 00:00:01 |
  |  10 |     TABLE ACCESS BY INDEX ROWID| YHN_DEPT       |     1 |    20 |     2   (0)| 00:00:01 |
  |* 11 |      INDEX RANGE SCAN          | DEPT_LOC_IDX   |     1 |       |     1   (0)| 00:00:01 |
  |* 12 |     INDEX RANGE SCAN           | EMP_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
  |  13 |    TABLE ACCESS BY INDEX ROWID | YHN_EMP        |     5 |   190 |     1   (0)| 00:00:01 |
  |  14 |   MERGE JOIN                   |                |     3 |   174 |     5  (20)| 00:00:01 |
  |* 15 |    TABLE ACCESS BY INDEX ROWID | YHN_DEPT       |     2 |    40 |     2   (0)| 00:00:01 |
  |  16 |     INDEX FULL SCAN            | DEPT_PK        |     4 |       |     1   (0)| 00:00:01 |
  |* 17 |    SORT JOIN                   |                |     4 |   152 |     3  (34)| 00:00:01 |
  |  18 |     TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     4 |   152 |     2   (0)| 00:00:01 |
  |* 19 |      INDEX RANGE SCAN          | EMP_JOB_IDX    |     4 |       |     1   (0)| 00:00:01 |
  |  20 |   MERGE JOIN                   |                |     2 |   116 |     5  (20)| 00:00:01 |
  |* 21 |    TABLE ACCESS BY INDEX ROWID | YHN_DEPT       |     2 |    40 |     2   (0)| 00:00:01 |
  |  22 |     INDEX FULL SCAN            | DEPT_PK        |     4 |       |     1   (0)| 00:00:01 |
  |* 23 |    SORT JOIN                   |                |     3 |   114 |     3  (34)| 00:00:01 |
  |  24 |     TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     3 |   114 |     2   (0)| 00:00:01 |
  |* 25 |      INDEX RANGE SCAN          | EMP_JOB_IDX    |     4 |       |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------------
  
  Outline Data
  -------------
    /*+
        (중간생략...)
        USE_CONCAT(@"MAIN" OR_PREDICATES(2))
        (중간생략...)
    */
  
▶ 기타 사항
  10g 이후 버전이더라도(?) 비교 연산자가 '=' 조건이 아닐 때는 일반적인 USE_CONCAT만으로도 같은
  컬럼에 대해 OR-Expansion이 잘 작동한다.

  SELECT /*+ USE_CONCAT */ * FROM YHN_EMP
   WHERE DEPTNO = 10 OR DEPTNO >= 30;
 
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                |     7 |   266 |     4   (0)| 00:00:01 |
  |   1 |  CONCATENATION               |                |       |       |            |          |
  |   2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     3 |   114 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     3 |       |     1   (0)| 00:00:01 |
  |   4 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     4 |   152 |     2   (0)| 00:00:01 |
  |*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     6 |       |     1   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     3 - access("DEPTNO"=10)
     5 - access("DEPTNO">=30)
         filter(LNNVL("DEPTNO"=10))
 
(4)NVL / DECODE 조건식애 대한 OR-Expansion
▶ 개념
  선택적 조건절(조건절이 들어올 수도 있고 생략될 수도 있음)이 있을 경우 Dynamic SQL을 사용하지 않고
  옵티마이저가 해결할 수 있는 방안으로 NVL, DECODE 함수를 이용하여 OR-Expansion 유도하는 쿼리변환

 

▶ TEST 환경 생성(ch4_22.txt 변형)
  CREATE INDEX EMP_ENAME_IDX ON YHN_EMP(ENAME);

 

▶ 9i부턴 아래의 쿼리를 작성하면 아래와 같은 OR-Expansion 쿼리 변환이 발생함
  SELECT /*+  */
         *
    FROM YHN_EMP
   WHERE DEPTNO = NVL(:DEPTNO, DEPTNO)
     AND ENAME  LIKE :ENAME || '%' ;
 
  ------------------------------------------------------------------------------------------------
  | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT              |                |     3 |   114 |     4   (0)| 00:00:01 |
  |   1 |  CONCATENATION                |                |       |       |            |          |
  |*  2 |   FILTER                      |                |       |       |            |          |
  |*  3 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     2 |    76 |     2   (0)| 00:00:01 |
  |*  4 |     INDEX RANGE SCAN          | EMP_ENAME_IDX  |     2 |       |     1   (0)| 00:00:01 |
  |*  5 |   FILTER                      |                |       |       |            |          |
  |*  6 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  7 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)| 00:00:01 |
  ------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(:DEPTNO IS NULL)
     3 - filter("DEPTNO" IS NOT NULL)
     4 - access("ENAME" LIKE :ENAME||'%')
         filter("ENAME" LIKE :ENAME||'%')
     5 - filter(:DEPTNO IS NOT NULL)
     6 - filter("ENAME" LIKE :ENAME||'%')
     7 - access("DEPTNO"=:DEPTNO)

 

▶ 8i로 테스트 했을 경우(참고)
  SELECT /*+ OPTIMIZER_FEATURES_ENABLE('8.1.7') */
         *
    FROM YHN_EMP
   WHERE DEPTNO = NVL(:DEPTNO, DEPTNO)
     AND ENAME  LIKE :ENAME || '%' ;
 
  ---------------------------------------------------------------
  | Id  | Operation           | Name    | Rows  | Bytes | Cost  |
  ---------------------------------------------------------------
  |   0 | SELECT STATEMENT    |         |     3 |   114 |     2 |
  |   1 |  CONCATENATION      |         |       |       |       |
  |*  2 |   FILTER            |         |       |       |       |
  |*  3 |    TABLE ACCESS FULL| YHN_EMP |     2 |    76 |     1 |
  |*  4 |   FILTER            |         |       |       |       |
  |*  5 |    TABLE ACCESS FULL| YHN_EMP |     1 |    38 |     1 |
  ---------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(:DEPTNO IS NULL)
     3 - filter("DEPTNO" IS NOT NULL AND "ENAME" LIKE :ENAME||'%')
     4 - filter(:DEPTNO IS NOT NULL)
     5 - filter("DEPTNO"=:DEPTNO AND "ENAME" LIKE :ENAME||'%')

 

▶ 아래의 SQL은 위의 SQL과 동일한 PLAN 형태임
  SELECT * FROM YHN_EMP
   WHERE :DEPTNO IS NULL
     AND DEPTNO IS NOT NULL
     AND ENAME  LIKE :ENAME || '%'
  UNION ALL
  SELECT * FROM YHN_EMP
   WHERE :DEPTNO IS NOT NULL
     AND DEPTNO = :DEPTNO
     AND ENAME  LIKE :ENAME || '%' ;
 
  ------------------------------------------------------------------------------------------------
  | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT              |                |     3 |   114 |     4  (50)| 00:00:01 |
  |   1 |  UNION-ALL                    |                |       |       |            |          |
  |*  2 |   FILTER                      |                |       |       |            |          |
  |*  3 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     2 |    76 |     2   (0)| 00:00:01 |
  |*  4 |     INDEX RANGE SCAN          | EMP_ENAME_IDX  |     2 |       |     1   (0)| 00:00:01 |
  |*  5 |   FILTER                      |                |       |       |            |          |
  |*  6 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  7 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)| 00:00:01 |
  ------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(:DEPTNO IS NULL)
     3 - filter("DEPTNO" IS NOT NULL)
     4 - access("ENAME" LIKE :ENAME||'%')
         filter("ENAME" LIKE :ENAME||'%')
     5 - filter(:DEPTNO IS NOT NULL)
     6 - filter("ENAME" LIKE :ENAME||'%')
     7 - access("DEPTNO"=TO_NUMBER(:DEPTNO))

 

▶ DECODE 함수를 사용하더라도 같은 처리가 발생함
  SELECT * FROM YHN_EMP
   WHERE DEPTNO = DECODE(:DEPTNO, NULL, DEPTNO, :DEPTNO)
     AND ENAME  LIKE :ENAME || '%' ;
    
  ------------------------------------------------------------------------------------------------
  | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT              |                |     3 |   114 |     4   (0)| 00:00:01 |
  |   1 |  CONCATENATION                |                |       |       |            |          |
  |*  2 |   FILTER                      |                |       |       |            |          |
  |*  3 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     2 |    76 |     2   (0)| 00:00:01 |
  |*  4 |     INDEX RANGE SCAN          | EMP_ENAME_IDX  |     2 |       |     1   (0)| 00:00:01 |
  |*  5 |   FILTER                      |                |       |       |            |          |
  |*  6 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP        |     1 |    38 |     2   (0)| 00:00:01 |
  |*  7 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)| 00:00:01 |
  ------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(:DEPTNO IS NULL)
     3 - filter("DEPTNO" IS NOT NULL)
     4 - access("ENAME" LIKE :ENAME||'%')
         filter("ENAME" LIKE :ENAME||'%')
     5 - filter(:DEPTNO IS NOT NULL)
     6 - filter("ENAME" LIKE :ENAME||'%')
     7 - access("DEPTNO"=:DEPTNO)

 

중요한 사실은 :deptno 변수 값의 입력 여부에 따라 위쪽 브랜치와 아래쪽 브랜치는
  각각 다른 인덱스를 사용한다는 사실이다.(★★★)
  (입력변수에 따라 공집합이 되는 쿼리블록이 구분되어 실행되거나 또는 값에 따라
   해당 조건의 인덱스를 사용할 수 있다는 점이다.)
  예전에는 튜너가 UNION ALL을 사용하여 수동으로 분기하였으나 옵티마이져가 스스로
  이와 같은 처리를 하려고 하지만 조건이 복잡해질수록 SQL 작성자가 직접 개입해서
  UNION ALL 로 분기해주어야 한다.