7. OR-Expansion
2011.06.01 23:38
07 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로 설정.
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-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 파라미터로 제어한다.
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 | 4장._쿼리_변환 | 휘휘 | 2011.09.07 | 2394 |
54 | 진행기록 | 운영자 | 2011.08.23 | 2383 |
53 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10122 |
52 | 3. 병렬 조인 | 오예스 | 2011.06.29 | 4969 |
51 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15614 |
50 | 5. 병렬 처리에 관한 기타 상식 [1] | balto | 2011.06.26 | 28162 |
49 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28273 |
48 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11862 |
47 | 7. Sort Area 크기 조정 | 오예스 | 2011.06.16 | 5986 |
46 | 5. 인덱스를 이용한 소트 연산 대체 | AskZZang | 2011.06.16 | 4955 |
45 | 4. 소트가 발생하지 않도록 SQL 작성 | AskZZang | 2011.06.16 | 3109 |
44 | 3. 데이터 모델 측면에서의 검토 | AskZZang | 2011.06.16 | 2724 |
43 | 5장. 소트 튜닝 | balto | 2011.06.16 | 4283 |
42 | 6. Sort Area를 적게 사용하도록 SQL 작성 | 오예스 | 2011.06.14 | 4209 |
41 | 1. 소트 수행 원리 | balto | 2011.06.12 | 8023 |
40 | 2. 소트를 발생시키는 오퍼레이션 | balto | 2011.06.12 | 4847 |
39 | 11. 집합 연산을 조인으로 변환 | 오라클잭 | 2011.06.08 | 4956 |
38 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9953 |
37 | 12. 기타 쿼리 변환 | 휘휘 | 2011.06.06 | 3122 |
» | 7. OR-Expansion | AskZZang | 2011.06.01 | 8333 |