1. HOME
  2. [종료]구루비 DB 스터디
  3. 2014 상반기 DB멘토링 스터디
  4. OR-Expansion,.

OR-Expansion,. (by aini79) [2014.05.31]

07 OR-Expansion


(1) OR-Expansion 기본

  • 아래 OR 조건의 쿼리가 그대로 수행된다면 Full Table Scan 또는 Index Combine 으로 처리됨

select * from emp
where job='CLERK' or deptno = 20
  • job과 deptno 에 각각 생성된 인덱스를 사용하려면 union all 형태로 바꿔준다.

select * from emp
where job='CLERK'
union all
select * from emp
where deptno = 20
and LNNVL(job='CLERK')
  • 사용자가 직접 쿼리를 바꿔주지 않아도 옵티마이저가 작업을 대신해 주는 경우를 'OR-Expansion'이라 함.
  • OR-Expansion 일어났을 때의 실행계획과 Predicate 정보

-----------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     7 |   224 |
|   1 |  CONCATENATION               |                |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     3 |    96 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     3 |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     4 |   128 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB"='CLERK')
   4 - filter(LNNVL("JOB"='CLERK'))
   5 - access("DEPTNO"=20)
  • 중복 액세스 되는 영역(deptno=20 이면서 job='CLERK')의 데이터 비중이 작을수록 효과적,
    그 반대라면 오히려 비용 증가
  • 테이블에 중복 액세스되더라도 결과집합에는 중복이 없게 하기 위해 오라클 내부적으로 LNNVL 함수 사용

  • OR-Expansion 제어 방법

파라미터: alter session set "_no_or_expansion" = false(기본값, OR-Expansion 사용) / true(기능 미사용)
          true이면 use_concat 힌트를 사용하더라도 OR-Expansion이 일어나지 않는다.
힌트: USE_CONCAT(OR-Expansion 유도), NO_EXPAND(OR-Expansion 방지)


(2) OR-Expansion 브랜치별 조인 순서 최적화

  • OR-Expansion에 의해 분기된 브랜치마다 각기 다른 조인 순서를 가질 수 있음



  • OR-Expansion 미사용

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');


-----------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     4 |   200 |
|   1 |  NESTED LOOPS                  |              |     4 |   200 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP          |    11 |   352 |
|*  3 |    INDEX RANGE SCAN            | EMP_SAL_IDX  |    11 |       |
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    18 |
|*  5 |      INDEX UNIQUE SCAN         | DEPT_PK      |     1 |       |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SAL">=2000)
   4   filter("E"."JOB"='SALESMAN' OR "D"."LOC"='DEPTNO')
   5 - access("D"."DEPTNO"="E"."DEPTNO")
  • emp 테이블에 sal >= 2000 조건으로 먼저 읽고 조인한 dept 테이블을 액세스하는 단계에서 e.job = 'SALESMAN' or d.loc = 'CHICAGO' 조건이 필터링
  • 드라이빙 조건(sal >= 2000)의 변별력이 나쁘고 OR로 묶인 두 조건의 변별력이 좋다면, 위 실행계획은 매우 비효율적



  • OR-Expansion 사용
  • emp.job, emp.deptno, dept.loc 컬럼에 인덱스 생성 후 실행

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: 2632617833

-------------------------------------------------------------------------------------------------
| 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| DEPT           |     1 |    20 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | DEPT_N1        |     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 | 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| 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          | PK_DEPT        |     1 |       |     0   (0)| 00:00:01 |
|* 13 |    TABLE ACCESS BY INDEX ROWID | 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



<쿼리1>
selec * from emp
where (depno=10 or deptno=30)
and ename = :ename

<쿼리2>
selec * from emp
where depno in (10,30)
and ename = :ename
  • 위 두 쿼리는 내용적으로 100% 동일
  • 아래와 같이 OR-Expansion 처리 가능

selec * from emp
where deptno = 30
and ename = :ename
union all
selec * from emp
where depno = 10
and ename = :ename

-----------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     2 |    74 |
|   1 |  CONCATENATION               |                |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    37 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    37 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
-----------------------------------------------------------------------

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 방식으로만 처리된다.
(억지로 use_concat 힌트를 이용하여 OR-Expansion을 유도할 수 있으나 IN-List Iterator 에 비해 나은점이 없으므로 그럴 이유가 없다.)
(i) 9i까지는 OR 조건이나 IN-List 를 힌트를 이용해 OR-Expansion으로 유도하면 뒤쪽에 놓인 값(위 쿼리에서는 30)이 항상 먼저 출력됐었다.
10g CPU 비용모델에서는 통계적으로 카디널리티가 작은 값이 먼저 출력된다.(9i 처럼 뒤쪽을 먼저 출력하려고 하면 ordered_predicates 힌트를 사용)
(i) 10g 이후 버전이더라도 비교 연산자가 '=' 조건이 아닐 때는 일반적인 use_concat 힌트만으로도 같은 컬럼에 대한 OR-Expansion이 잘 작동한다.


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


select * from emp
where deptno = nvl(:deptno, deptno)
and ename like :ename || '%'

또는 

select * from emp
where deptno = decode(:deptno, null, 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 || '%'
  • deptno 변수값의 null 여부에 따라 위 또는 아래쪽 브랜치만 수행하는것
  • 실행계획은 아래와 같다.

----------------------------------------------------------------------
| Id | Operation                       |Name           |Rows | Bytes |
----------------------------------------------------------------------
| 0  | SELECT STATEMENT                |               |   3 |   111 |
| 1  |  CONCATENATION                  |               |     |       |
| 2  |   FILTER                        |               |     |       |
| 3  |    TABLE ACCESS (BY INDEX ROWID)|EMP            |   2 |    74 |
| 4  |     INDEX (RANGE SCAN)          |EMP_ENAME_IDX  |   2 |       |
| 5  |   FILTER                        |               |     |       |
| 6  |    TABLE ACCESS (BY INDEX ROWID)|EMP            |   1 |    37 |
| 7  |     INDEX (RANGE SCAN)          |EMP_DEPTNO_IDX |   5 |       |
----------------------------------------------------------------------
  • :deptno 변수 값 입력 여부에 따라 다른 인덱스를 사용한다

:deptno에 null값을 입력했을 때(위쪽 브랜치)는 EMP_ENAME_IDX 사용, null값이 아닌 경우(아래쪽 브랜치) EMP_DEPTNO_IDX 사용

  • _or_expand_nvl_predicate 파라미터로 제어
  • nvl,decode를 여러 컬럼에 사용한 경우 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어나기 때문에 옵션조건이 복잡할 때는 수동으로 union all 분기를 해 줘야만 한다.
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2014 상반기 DB멘토링 스터디
  4. OR-Expansion,.