7. OR-Expansion

조회 수 7234 추천 수 0 2011.06.01 14:38:25
AskZZang *.33.233.130

07 OR-Expansion

 

 (1)   OR-Expansion 기본

 

사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 OR 조건을 Full Table Scan 처리가 아닌 union all 형태로 변경 처리해주는 경우가 있는데 이를 OR-Expansion 이라 한다.

 

중복 액세스되더라도 결과집합에는 중복이 없게 하려고 union all 아래쪽에 오라클이 내부적으로 LNNVL 함수를 사용한다.

이 함수는 조건식이 false 이거나 알 수 없는(Unknown) 값일 때 true 를 리턴한다.

 

힌트)

Use_concat : OR-Expansion을 유도하고자 할 때 사용

No_expand : OR-Expansion을 방지하고자 할 때 사용

 

OR-Expansion 기능을 아예 작동하지 못하도록 하려면 _no_or_expansion parameter true로 설정.

 

 

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

 

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-ListOR-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 파라미터로 제어한다.

 

Nvl 또는 decode를 여러 컬럼에 대해 사용했을 때는 그 중 변별력이 가장 좋은 컬럼 기준으로 한 번만 분기가 일어난다. 옵션 조건이 복잡할 때는 이 방식에만 의존하기 어려운 이유가 여기에 있고, 그럴 때는 여전히 수동으로 union all 분기를 해 줘야만 한다.