[Oracle]OR-Expansion

개념

기본적으로 OR로 묶인 where 조건의 경우 OR의 사용으로 INDEX를 사용할 수 없어 FULL TABLE SCAN을 해야한다. 그러나 OR-Expansion을 사용하게 되면 INDEX를 사용할 수 있게 되면서 해당 결과 집합을 UNION ALL로 합쳐서 사용한다.

 

즉, 결론적으로 INDEX를 사용하며 UNION ALL로 합쳐주는 작업으로 이해하면 쉽다. 하지만 테이블을 2번 ACCESS한다는 단점이 있어서 겹치는 부분이 적을때 효과적이다. 

 

HINT

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

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

 

• alter session set "_no_or_expansion"=true; 로도 설정가능

 

-힌트사용(use_concat) 옵티마이저에 의한 쿼리변환 
select /*+ use_concat */* from emp
where job='CLERK'  or  deptno=20;

Execution Plan
----------------------------------------------------------
Plan hash value: 668283641

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     8 |   304 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     4 |   152 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     4 |   152 |     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)

 

 

분기된 쿼리가 각각 다른 인덱스를 사용하긴 하지만 emp 테이블의 경우 엑세스가 두번 일어난다. 

중복 엑세스되는 영역의 비중이 작을수록 효과적이고, 그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다. 

중복엑세스 되더라도 결과집합에는 중복이 없게 하려고 내부적으로 LNNVL 함수를 사용한것을 볼 수 있다. 

* LNNVL 함수 : 두 쿼리의 교집합이 두번 출력되는 것을 방지하기 위해서 OR-Expansion 사용시 자동으로 사용되는 함수

job<>'CLERK' 이거나 job is null 인 집합만 읽으려는 것이며 
이 함수는 조건식이 false 이거나 알수없는 값일때 true를 리턴한다.
  LNNVL(1=1)    : FALSE
  LNNVL(1=2)    : TRUE
  LNNVL(Null=1) : TRUE

 

 

같은 컬럼에 대한 OR-Expansion

-OR절 
select  *
  from emp
 where (deptno = 10 or deptno = 30)
   and ename = 'CLARK';

Execution Plan
----------------------------------------------------------
Plan hash value: 1707373705

-----------------------------------------------------------------------------------------------
| 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| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     9 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10 OR "DEPTNO"=30)

-OR OR-Expansion 유도
-use_concat 힌트에 아래와 같이 인자를 제공하여  유도할수 있다 

select /*+  qb_name(MAIN) use_concat(@MAIN 1) */ *
  from emp e
 where (deptno = 10 or deptno = 30)
   and ename = 'CLARK';

Execution Plan
----------------------------------------------------------
Plan hash value: 809118877

-----------------------------------------------------------------------------------------------
| 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| 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| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10)
   4 - filter("ENAME"='CLARK')
   5 - access("DEPTNO"=30)



-IN 절 
select  *
  from emp
 where deptno in (10, 30)
   and ename = 'CLARK';



Execution Plan
----------------------------------------------------------
Plan hash value: 1707373705

-----------------------------------------------------------------------------------------------
| 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| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     9 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10 OR "DEPTNO"=30)

-IN 절  OR-Expansion 유도
select /*+  qb_name(MAIN) use_concat(@MAIN 1) */ *
  from emp
 where deptno in (10, 30)
   and ename = 'CLARK';


Execution Plan
----------------------------------------------------------
Plan hash value: 809118877

-----------------------------------------------------------------------------------------------
| 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| 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| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10)
   4 - filter("ENAME"='CLARK')
   5 - access("DEPTNO"=30)


-하지만 or-expansion을 유도해도 나아지는 점이 없으므로 굳이 그렇게 할 이유가 없다.

 

9i 까지는 같은 컬럼에 대한 or 조건이나 in-list도 OR-Expansion이 작동할 수 있었지만, 10g 부터는 기존적으로 아래와 같이 In-List Iterator 방식으로만 처리된다. 만약 억지로 OR-Expansion으로 유도하려면 Use_Concat 힌트에 qb_name 인자를 제공하면 되지만, In-List Iterator에 비해 나은 점이 없으므로 굳이 그렇게 할 이유는 없다. 

 

* 기본적으로 index가 있어야 OR-Expansion 이 가능하다. 없으면 실행계획에 concatenation 실행계획이 발생하지 않음!

 

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이 잘 작동한다.

 

NVL/DECODE 함수를 사용한 조건식에 대한 OR-Expansion 

조건절에 NVL이나 DECODE 함수를 사용해도 OR-Expansion 실행계획이 발생할 수 있다.

해당 변수 값의 NULL 여부에 따라 위 또는 아래쪽 브랜치만 수행하는 방식으로 무엇보다 중요한 것은 변수 값의 입력 여부에 따라 다른 인덱스를 사용가능하다는 사실이다.

이 유용한 기능을 제어하는 파라미터는 '_or_expand_nvl_predicate'  가 있다. 

 

 

NVL

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

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=114)
   1    0   CONCATENATION
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=2 Bytes=76)
   4    3         INDEX (RANGE SCAN) OF 'SCOTT.EMP_N4' (INDEX) (Cost=1 Card=2)
   5    1     FILTER
   6    5       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=1 Bytes=38)
   7    6         INDEX (RANGE SCAN) OF 'SCOTT.EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
   4 - filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=:DEPTNO)
-----------------------------------------------------------

-위쪽 브렌치는 EMP_N4 사용
-아래 브렌치는 EMP_DEPTNO_IDX 사용


-위와 같은 형태로 쿼리를 작성하면 오라클 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 || '%';
   
 Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=114)
   1    0   UNION-ALL
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=2 Bytes=76)
   4    3         INDEX (RANGE SCAN) OF 'SCOTT.EMP_N4' (INDEX) (Cost=1 Card=2)
   5    1     FILTER
   6    5       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=1 Bytes=38)
   7    6         INDEX (RANGE SCAN) OF 'SCOTT.EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
   4 - filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=TO_NUMBER(:DEPTNO))
-----------------------------------------------------------
-deptno 변수값의 null 여부에 따라 위 또는 아래쪽 브렌치만 수행하는것이다.
-decode 함수를 사용하더라도 같은 처리가 일어난다

 

 

DECODE

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

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=114)
   1    0   CONCATENATION
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=2 Bytes=76)
   4    3         INDEX (RANGE SCAN) OF 'SCOTT.EMP_N4' (INDEX) (Cost=1 Card=2)
   5    1     FILTER
   6    5       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=1 Bytes=38)
   7    6         INDEX (RANGE SCAN) OF 'SCOTT.EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
   4 - filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=:DEPTNO)
-----------------------------------------------------------

-dbptno 변수값의 입력 여부에 따라 다른 인덱스를 사용한다
-deptno 변수에 null 이 들어오면 위쪽 EMP_N4를 사용하고, null값이 아닌 값이 들어오면 EMP_DEPTNO_IDX를 사용하게 된다

 

주의할 점은 NVL 또는 DECODE를 여러 컬럼에서 사용했을때는 그 중 변별력이 가장 좋은 컬럼을 기준으로 한번만 분기가 일어난다. 이러한 이유로 옵션 조건이 복잡할 때는 이 방식에만 의존하기 어렵고 그럴 때는 여전히 수동으로 UNION ALL 분기를 해주는 것이 필요하다.