select /*+ NO_EXPAND */ * from emp e, dept d

where  d.deptno = e.deptno

and    e.sal >= 2000

and   (e.job = 'SALESMAN' or d.loc = 'CHICAGO');

no_expand 힌트를 사용해 OR-Expansion을 하지 못하도록 막았으므로

sal >= 2000 조건으로 emp 테이블을 먼저 읽어 조인할 후에

dept 테이블을 액세스하는 단계에서 e.job = 'SALESMAN' or d.loc = 'CHICAGO' 조건 필터링.

만약 드라이빙 조건인 sal >= 2000의 변별력이 나빠 조인 액세스 건수가 많고 오히려 최종 필터되는 OR로 묶인 두 조건의 변별력이 좋다면, 위 실행계획은 매우 비효율적이다.

select /*+ USE_CONCAT */ * from emp e, dept d

where  d.deptno = e.deptno

and    e.sal >= 2000

and   (e.job = 'SALESMAN'or d.loc = 'CHICAGO');

Execution Plan

----------------------------------------------------------

Plan hash value: 2242298629

-----------------------------------------------------------------------------------------

| Id  | Operation                | Name      | Rows   | Bytes | Cost (%CPU)| Time        |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT         | |     6 |   348 |    13   (8)| 00:00:01 |

|   1 |  CONCATENATION                   | |        |        |             |            |

|   2 |   MERGE JOIN                     | |     4 |   232 |     7  (15)| 00:00:01 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     2   (0)| 00:00:01 |

|   4 |     INDEX FULL SCAN              | PK_DEPT |     4 |  |     1   (0)| 00:00:01 |

|*  5 |    SORT JOIN                     | |    11 |   418 |     5  (20)| 00:00:01 |

|*  6 |     TABLE ACCESS FULL            | EMP       |    11 |   418 |     4   (0)| 00:00:01 |

|   7 |   NESTED LOOPS                   | |        |        |             |            |

|   8 |    NESTED LOOPS          | |     2 |   116 |     6   (0)| 00:00:01 |

|*  9 |     TABLE ACCESS FULL            | EMP       |     2 |    76 |     4   (0)| 00:00:01 |

|* 10 |     INDEX UNIQUE SCAN            | PK_DEPT |     1 |  |     0   (0)| 00:00:01 |

|* 11 |    TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("D"."LOC"='CHICAGO')

   5 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

   6 - filter("E"."SAL">=2000)

   9 - filter("E"."JOB"='SALESMAN' AND "E"."SAL">=2000)

  10 - access("D"."DEPTNO"="E"."DEPTNO")

  11 - filter(LNNVL("D"."LOC"='CHICAGO'))

Loc = ‘CHICAGO’인 집합을 생성하는 쿼리와 job = ‘SALESMAN’ 인 집합을 생성하는 쿼리가 각기 다른 인덱스와 조인 순서를 가지고 실행된다.

두 쿼리의 교집합이 두 번 출력되는 것을 방지하려고 LNNVL 함수가 사용되었다.

(3)   같은 컬럼에 대한 OR-Expansion

select  * from emp

where  (deptno = 10 or deptno = 30)

and    ename = :ename;

select * from emp

where  deptno in (10, 30)

and    ename = :ename;

select * from emp

where  deptno = 30

and    ename = :ename

union all

select * from emp    

where  deptno = 10

and    ename = :ename ;

9i까지는 위와 같은 형태 즉, 같은 컬럼에 대한 OR 조건이나 IN-List도 OR-Expansion이 작동할 수 있었지만 10g 부터는 기본적으로 IN-List Iterator 방식으로만 처리된다.

주의) 9i까지는 OR조건이나 IN-List를 힌트를 이용해 OR-Expansion으로 유도하면 뒤쪽에 놓인 값이 항상 먼저 출력됐었다.

하지만 10g CPU 비용 모델에서는 위와 같이 OR-Expansion으로 유도했을 때 통계적으로 카디널리티가 작은 값이 먼저 출력된다. 9i 이전처럼 뒤쪽에 놓인 값이 먼저 출력되도록 하려면 ordered_predicates 힌트를 사용하거나 IO 비용 모델로 바꿔줘야 한다.

10g 이후 버전이더라도 비교 연산자가 ‘=’ 조건이 아닐 때는 일반적인 use_concat 힌트만으로도 같은 컬럼에 대한 OR-Expansion이 잘 작동한다.

(4)   nvl/decode 조건식에 대한 OR-Expansion

사용자가 선택적으로 입력하는 조건절에 대해 nvl 또는 decode 함수를 이용할 수 있다.

select * from emp

where  deptno = nvl(:deptno, deptno)

and    ename  like :ename || '%' ;

오라클 9i부터 쿼리를 위와 같이 작성하면, 아래와 같은 형태로 OR-Expansion 쿼리 변환이 일어난다.

select * from emp

where  :deptno is null

and    deptno is not null

and    ename  like :ename || '%'

union all

select * from emp

where  :deptno is not null

and    deptno = :deptno

and    ename  like :ename || '%' ;

아래와 같이 decode 함수를 사용하더라도 같은 처리가 일어난다.

select * from emp

where  deptno = decode(:deptno, null, deptno, :deptno)

and    ename  like :ename || '%' ;

무엇보다 중요한 것은 :deptno 변수 값 입력 여부에 따라 다른 인덱스를 사용한다는 사실이다.

실행계획을 보면 :deptno 변수에 null 값을 입력했을 때 사용되는 위쪽 브랜치는 emp_ename_idx 인덱스를 사용했고, null 값이 아닌 값을 입력했을 때 사용되는 아래쪽 브랜치는 emp_deptno_idx 인덱스를 사용했다.

_or_expand_nvl_predicate 파라미터로 제어한다.