h5.(1)OR-Expansion 기본
아래 쿼리가 그대로 수행된다면 OR 조건이므로 Full Table Scan으로 처리 될것이다.
select * from emp
where job = 'CLERK' or deptno = 20
job과 deptno에 생성된 인덱스를 사용하고 싶다면 아래와 같이 union all 형태로 바꿔주면 된다.
select * from emp
where job = 'CLERK'
union all
select * frαn emp
where deptno = 20
and LNNVL(job='CLERK')
select /*+ use_concat */* from emp
where job = 'CLERK' or deptno = 20;
select /*+ no_expand */* from emp
where job = 'CLERK' or deptno = 20;
OR-Expansion 기능을 아예 작동하지 못하도록 막으려면
alter session set "_no_or_expansion" = trun;
h5.(2)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("E"."SAL">=2000)
5 - filter("E"."JOB"='SALESMAN' OR "D"."LOC"='CHICAGO')
4 - access("D"."DEPTNO"="E"."DEPTNO")
드라이빙 조건(sql>= 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')
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 300 |
| 1 | CONCATENATION | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | EMP | 4 | 128 |
| 3 | NESTED LOOPS | | 4 | 200 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 18 |
|* 5 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
| 7 | NESTED LOOPS | | 2 | 100 |
|* 8 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 64 |
|* 9 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | |
|* 10 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 18 |
|* 11 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("E"."SAl">=2000)
5 - access("D"."LOC"='CHICAGO')
6 - access("D"."DEPTNO"="E"."DEPTNO")
8 - filter("E"."SAL">=2000)
9 - access("E"."JOB"='SALESMAN' )
10 - filter(LNNVL("D"."LOC"='CHICAGO'))
11 - access("D"."DEPTNO"="E"."DEPTNO")
LOC = 'CHICAGO'인 집합을 생성하는 쿼리와 JOB = 'SALESMAN' 인 집합을 생성하는 쿼리가 각기 다른 인덱스와 조인 순서를 가지고 실행된다.
두 쿼리의 교집합이 두 번 출력되는 것을 방지하려고 LNNVL 함수가 사용
h5.(3)같은 컬럼에 대한 OR-Expansion
<쿼리1>
select * from emp
where (deptno = 10 or deptno = 30)
and ename = :ename
<쿼리2>
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 방식으로만 처리된다.
만약 억지로 OR-Expansion으로 유도하려면 use_concat 힌트에 qb_name 인자를 제공하면 되지만, IN-List Iterator에 비해 나은 점이 없다
9i까지는 OR 조건이나 IN-List를 힌트로 이용해 OR-Expansion으로 유도하면 뒤쪽에 놓인 조건이 항상 먼저 출력.
10g CPU 비용 모델에서는 위와 같이 OR-Expansion으로 유도했을 때 통계적으로 카디널리티가 작은 값이 먼저 출력.
10g 이후 버전에서도 비교 연산자가 '=' 조건이 아닐 때는 일반적인 use_concat 힌트만으로도 같은 컬럼에 대한 OR-Expansion이 작동한다.
h5.(3)nvl/decode 조건식에 대한 or-expasion
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 || '%'
select * from emp
where deptno = decode(:deptno, null, deptno, :deptno)
and ename like :ename || '%'
--------------------------------------------------------------------
| 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 분기를 해 줘야만 한다.