7. OR-Expansion
2011.05.31 11:22
7.OR-Expansion
(1)OR-Expansion 기본
▶ OR-Expansion의 개념은 Where 절에서 OR 조건이나 IN 조건을 마나면 Union All로 변형되는 경우를 말한다.
같은 컬럼에 대하여 OR 조건이 발생한다면 대부분의 경우 Inlist Iterator Plan으로 처리된다.
하지만 OR로 분기되는 컬럼(조건)이 다른 경우는 Inlist Iterator가 발생할 수 없고 Union All로만
변환이 가능하다.
▶ TEST 환경 생성(ch4_19.txt 변형)
CREATE TABLE YHN_EMP AS SELECT * FROM SCOTT.EMP;
CREATE INDEX EMP_DEPTNO_IDX ON YHN_EMP(DEPTNO);
CREATE INDEX EMP_JOB_IDX ON YHN_EMP(JOB);
SELECT * FROM YHN_EMP
WHERE JOB = 'CLERK'
OR DEPTNO = 20;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 609 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | YHN_EMP | 7 | 609 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IDX | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("JOB"='CLERK')
7 - access("DEPTNO"=20)
▶ OR 대신 AND 사용시...(참고)
SELECT * FROM YHN_EMP
WHERE JOB = 'CLERK'
AND DEPTNO = 20;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | YHN_EMP | 2 | 174 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | EMP_JOB_IDX | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPTNO"=20)
7 - access("JOB"='CLERK')
ALTER SESSION SET "_B_TREE_BITMAP_PLANS" = FALSE;
SELECT * FROM YHN_EMP
WHERE JOB = 'CLERK'
OR DEPTNO = 20;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 609 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| YHN_EMP | 7 | 609 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='CLERK' OR "DEPTNO"=20)
▶ 만약 JOB과 DEPTNO에 각각 생성된 인덱스를 사용하고 싶다면 아래와 같이 UNION ALL 형태로 바꿔야 한다.
SELECT * FROM YHN_EMP
WHERE JOB = 'CLERK'
UNION ALL
SELECT * FROM YHN_EMP
WHERE DEPTNO = 20
AND LNNVL(JOB = 'CLERK');
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 609 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 4 | 348 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IDX | 4 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 3 | 261 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOB"='CLERK')
4 - filter(LNNVL("JOB"='CLERK'))
5 - access("DEPTNO"=20)
▶ JOB과 DEPTNO 컬럼을 선두로 갖는 두 인덱스가 각각 사용됨
분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, YHN_EMP 테이블 액세스가 두번 발생함
따라서 중복 액세스되는 영역(DEPTNO = 20 이면서 JOB = 'CLERK')의 데이터 비중이 작을 수록 효과적
그 반대의 경우라면 오히려 비용이 증가되어 비효율 적임
또한, UNION ALL의 중복을 없게 하려고 UNION ALL의 아래쪽에 오라클이 내부적으로 LNNVL 함수를 사용
▶ LNNVL 함수
인수 값이 FALSE 이거나 UNKNOWN(알 수 없는 eg. NULL) 값일 때 TRUE 반환, TRUE 일 때는 FALSE 반환
▶ OR-Expansion의 제어 힌트
USE_CONCAT : OR-Expansion을 유도하고자 할 때 사용
NO_EXPAND : OR-Expansion의 기능을 방지하고자 할 때 사용
SELECT /*+ USE_CONCAT */ *
FROM YHN_EMP
WHERE JOB = 'CLERK'
OR DEPTNO = 20;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 609 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 5 | 435 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 2 | 174 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=20)
4 - filter(LNNVL("DEPTNO"=20))
5 - access("JOB"='CLERK')
(2)OR-Expansion 브랜치별 조인 순서 최적화
▶ TEST 환경 생성(ch4_20.txt 변형)
DROP TABLE YHN_DEPT;
DROP TABLE YHN_EMP ;
CREATE TABLE YHN_DEPT AS SELECT * FROM SCOTT.DEPT;
CREATE TABLE YHN_EMP AS SELECT * FROM SCOTT.EMP;
ALTER TABLE YHN_DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);
CREATE INDEX EMP_SAL_IDX ON YHN_EMP(SAL);
SELECT /*+ NO_EXPAND */ *
FROM YHN_EMP E, YHN_DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND E.SAL >= 2000
AND (E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO');
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 116 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 2 | 116 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | YHN_DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 6 | 228 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 6 | 228 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_SAL_IDX | 6 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E"."JOB"='SALESMAN' OR "D"."LOC"='CHICAGO')
5 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
7 - access("E"."SAL">=2000)
▶ 책 내용과 PLAN이 다름
SELECT /*+ ORDERED USE_NL(E, D) */ *
FROM YHN_EMP E, YHN_DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND E.SAL >= 2000
AND (E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO');
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 116 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 116 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 6 | 228 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_SAL_IDX | 6 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | YHN_DEPT | 1 | 20 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."SAL">=2000)
5 - access("D"."DEPTNO"="E"."DEPTNO")
6 - filter("E"."JOB"='SALESMAN' OR "D"."LOC"='CHICAGO')
▶ 책 내용과는 조금 다르지만.. NO_EXPAND를 이용해 OR-Expansion 하지 못하도록 막았다고 가정하자.
SAL >= 2000 조건으로 EMP 테이블을 먼저 읽어 조인한 후 DEPT 테이블을 ACCESS하는 단계에서
(E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO') 조건이 필터링으로 이루어지고 있다.
만약, 드라이빙 조건(SAL >= 2000)의 변별력이 나빠 조인 액세스 건수가 많고, 필터되는 OR로
묶인 두 조건의 변별력이 좋다면 위 실행계획은 비효율적임
▶ TEST 환경 생성(ch4_20.txt 변형)
EMP 테이블에 JOB, DEPTNO - 각각 인덱스 생성
DEPT 테이블에 LOC - 추가 인덱스 생성
DROP INDEX EMP_JOB_IDX;
DROP INDEX EMP_DEPTNO_IDX;
DROP INDEX DEPT_LOC_IDX;
CREATE INDEX EMP_JOB_IDX ON YHN_EMP(JOB);
CREATE INDEX EMP_DEPTNO_IDX ON YHN_EMP(DEPTNO);
CREATE INDEX DEPT_LOC_IDX ON YHN_DEPT(LOC);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'YHN_EMP' );
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'YHN_DEPT');
SELECT /*+ USE_CONCAT */ *
FROM YHN_EMP E, YHN_DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND E.SAL >= 2000
AND (E.JOB = 'SALESMAN' OR D.LOC = 'CHICAGO');
-------------------------------------------------------------------------------------------------
| 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| YHN_DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_LOC_IDX | 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 | YHN_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| YHN_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 | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | YHN_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
▶ 아래의 두 쿼리는 100% 동일한 쿼리임
SELECT * FROM YHN_EMP
WHERE (DEPTNO = 10 OR DEPTNO = 30)
AND ENAME = :ENAME;
SELECT * FROM YHN_EMP
WHERE DEPTNO IN (10, 30)
AND ENAME = :ENAME;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 9 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
따라서, 아래와 같이 OR-Expansion 처리가 가능함
SELECT * FROM YHN_EMP
WHERE DEPTNO = 30
AND ENAME = :ENAME
UNION ALL
SELECT * FROM YHN_EMP
WHERE DEPTNO = 10
AND ENAME = :ENAME ;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
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 방식으로만 처리됨
억지로 OR-Expansion을 유도하려면 USE_CONCAT 힌트에 인자를 제공하면 되지만 IN-List에 비해 나은 점이
없으므로 굳이 그렇게 할 이유가 없다.
SELECT /*+ QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */* FROM YHN_EMP
WHERE (DEPTNO = 10 OR DEPTNO = 30)
AND ENAME = :ENAME;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
▶ USE_CONCAT 힌트의 인자 값(참고)
Outline Data 블럭을 보면 USE_CONCAT 힌트의 인자에 쿼리블럭명과 숫자가 사용됨
2가지 패턴이 있음
① 8 : 분기 조건단위로 UNION ALL로 분리하라는 의미. DEFAULT
② 1 : UNION ALL로 분리가 가능하다면 최대한 분리하라는 의미임
SELECT /*+ QB_NAME(MAIN) USE_CONCAT */ *
FROM YHN_EMP E, YHN_DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND (E.JOB IN ('SALESMAN', 'CLERK') OR D.LOC IN ('CHICAGO', 'DALLAS'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 870 | 9 (12)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 9 | 522 | 4 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| YHN_DEPT | 2 | 40 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | DEPT_LOC_IDX | 2 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | YHN_EMP | 5 | 190 | 1 (0)| 00:00:01 |
| 9 | MERGE JOIN | | 6 | 348 | 5 (20)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | YHN_DEPT | 2 | 40 | 2 (0)| 00:00:01 |
| 11 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 12 | SORT JOIN | | 8 | 304 | 3 (34)| 00:00:01 |
| 13 | INLIST ITERATOR | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 8 | 304 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | EMP_JOB_IDX | 8 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
(중간생략...)
USE_CONCAT(@"MAIN" 8 OR_PREDICATES(2))
(중간생략...)
*/
SELECT /*+ QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */ *
FROM YHN_EMP E, YHN_DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND (E.JOB IN ('SALESMAN', 'CLERK') OR D.LOC IN ('CHICAGO', 'DALLAS'));
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 16 (13)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 5 | 290 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| YHN_DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_LOC_IDX | 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 | YHN_EMP | 5 | 190 | 1 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 4 | 232 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| YHN_DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | YHN_EMP | 5 | 190 | 1 (0)| 00:00:01 |
| 14 | MERGE JOIN | | 3 | 174 | 5 (20)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | YHN_DEPT | 2 | 40 | 2 (0)| 00:00:01 |
| 16 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 17 | SORT JOIN | | 4 | 152 | 3 (34)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 4 | 152 | 2 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | EMP_JOB_IDX | 4 | | 1 (0)| 00:00:01 |
| 20 | MERGE JOIN | | 2 | 116 | 5 (20)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | YHN_DEPT | 2 | 40 | 2 (0)| 00:00:01 |
| 22 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 23 | SORT JOIN | | 3 | 114 | 3 (34)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | EMP_JOB_IDX | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
(중간생략...)
USE_CONCAT(@"MAIN" OR_PREDICATES(2))
(중간생략...)
*/
▶ 기타 사항
10g 이후 버전이더라도(?) 비교 연산자가 '=' 조건이 아닐 때는 일반적인 USE_CONCAT만으로도 같은
컬럼에 대해 OR-Expansion이 잘 작동한다.
SELECT /*+ USE_CONCAT */ * FROM YHN_EMP
WHERE DEPTNO = 10 OR DEPTNO >= 30;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 266 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 4 | 152 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
5 - access("DEPTNO">=30)
filter(LNNVL("DEPTNO"=10))
(4)NVL / DECODE 조건식애 대한 OR-Expansion
▶ 개념
선택적 조건절(조건절이 들어올 수도 있고 생략될 수도 있음)이 있을 경우 Dynamic SQL을 사용하지 않고
옵티마이저가 해결할 수 있는 방안으로 NVL, DECODE 함수를 이용하여 OR-Expansion 유도하는 쿼리변환
▶ TEST 환경 생성(ch4_22.txt 변형)
CREATE INDEX EMP_ENAME_IDX ON YHN_EMP(ENAME);
▶ 9i부턴 아래의 쿼리를 작성하면 아래와 같은 OR-Expansion 쿼리 변환이 발생함
SELECT /*+ */
*
FROM YHN_EMP
WHERE DEPTNO = NVL(:DEPTNO, DEPTNO)
AND ENAME LIKE :ENAME || '%' ;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 2 | 76 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_ENAME_IDX | 2 | | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:DEPTNO IS NULL)
3 - filter("DEPTNO" IS NOT NULL)
4 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
5 - filter(:DEPTNO IS NOT NULL)
6 - filter("ENAME" LIKE :ENAME||'%')
7 - access("DEPTNO"=:DEPTNO)
▶ 8i로 테스트 했을 경우(참고)
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('8.1.7') */
*
FROM YHN_EMP
WHERE DEPTNO = NVL(:DEPTNO, DEPTNO)
AND ENAME LIKE :ENAME || '%' ;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 |
| 1 | CONCATENATION | | | | |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS FULL| YHN_EMP | 2 | 76 | 1 |
|* 4 | FILTER | | | | |
|* 5 | TABLE ACCESS FULL| YHN_EMP | 1 | 38 | 1 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:DEPTNO IS NULL)
3 - filter("DEPTNO" IS NOT NULL AND "ENAME" LIKE :ENAME||'%')
4 - filter(:DEPTNO IS NOT NULL)
5 - filter("DEPTNO"=:DEPTNO AND "ENAME" LIKE :ENAME||'%')
▶ 아래의 SQL은 위의 SQL과 동일한 PLAN 형태임
SELECT * FROM YHN_EMP
WHERE :DEPTNO IS NULL
AND DEPTNO IS NOT NULL
AND ENAME LIKE :ENAME || '%'
UNION ALL
SELECT * FROM YHN_EMP
WHERE :DEPTNO IS NOT NULL
AND DEPTNO = :DEPTNO
AND ENAME LIKE :ENAME || '%' ;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 2 | 76 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_ENAME_IDX | 2 | | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:DEPTNO IS NULL)
3 - filter("DEPTNO" IS NOT NULL)
4 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
5 - filter(:DEPTNO IS NOT NULL)
6 - filter("ENAME" LIKE :ENAME||'%')
7 - access("DEPTNO"=TO_NUMBER(:DEPTNO))
▶ DECODE 함수를 사용하더라도 같은 처리가 발생함
SELECT * FROM YHN_EMP
WHERE DEPTNO = DECODE(:DEPTNO, NULL, DEPTNO, :DEPTNO)
AND ENAME LIKE :ENAME || '%' ;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 2 | 76 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_ENAME_IDX | 2 | | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:DEPTNO IS NULL)
3 - filter("DEPTNO" IS NOT NULL)
4 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
5 - filter(:DEPTNO IS NOT NULL)
6 - filter("ENAME" LIKE :ENAME||'%')
7 - access("DEPTNO"=:DEPTNO)
▶ 중요한 사실은 :deptno 변수 값의 입력 여부에 따라 위쪽 브랜치와 아래쪽 브랜치는
각각 다른 인덱스를 사용한다는 사실이다.(★★★)
(입력변수에 따라 공집합이 되는 쿼리블록이 구분되어 실행되거나 또는 값에 따라
해당 조건의 인덱스를 사용할 수 있다는 점이다.)
예전에는 튜너가 UNION ALL을 사용하여 수동으로 분기하였으나 옵티마이져가 스스로
이와 같은 처리를 하려고 하지만 조건이 복잡해질수록 SQL 작성자가 직접 개입해서
UNION ALL 로 분기해주어야 한다.
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 20 | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3355 |
| 19 | 6. IOT, 클러스터 테이블 활용 | 휘휘 | 2011.02.26 | 2751 |
| 18 | 6. 히스토그램 | 실천하자 | 2011.04.25 | 10917 |
| 17 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4595 |
| 16 |
6. Sort Area를 적게 사용하도록 SQL 작성
 | 실천하자 | 2011.06.14 | 8857 |
| 15 | 7. 인덱스 스캔 효율 [1] | 휘휘 | 2011.03.09 | 16887 |
| 14 | 7. 비용 | 휘휘 | 2011.05.03 | 6167 |
| » | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 8718 |
| 12 | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.14 | 15065 |
| 11 | 8. 인덱스 설계 | 멋진넘 | 2011.03.07 | 8345 |
| 10 | 7. 조인을 내포한 DML 튜닝 | 실천하자 | 2011.04.04 | 7171 |
| 9 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.30 | 31071 |
| 8 | 8. 공통 표현식 제거 | darkbeom | 2011.06.07 | 5220 |
| 7 |
9. 비트맵 인덱스
| 실천하자 | 2011.03.06 | 12340 |
| 6 |
8. 고급 조인 테크닉-1
[1] | darkbeom | 2011.04.04 | 13265 |
| 5 | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.07 | 7834 |
| 4 |
8. 고급 조인 테크닉-2
| suspace | 2011.04.05 | 7017 |
| 3 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5531 |
| 2 | 11. 집합 연산을 조인으로 변환 | suspace | 2011.06.08 | 5994 |
| 1 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.03 | 6646 |