07 OR - Expansion

(1)OR - Expansion 기본

select * from emp
where job='CLERK' or deptno = 20

• 위 쿼리가 그대로 수행 되면 or 조건으로 인해 Full Table Scan으로 처리 될 것으로 보인다.

• job과 deptno에 각각 생성된 인덱스를 사용하기 위해서는 union all 으로 변경해주면 된다.

select * from emp
where job='CLERK'
union all
select * from emp
where deptno = 20
and LNNVL(job='CLERK')

• 사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 그런 작업을 대신해 주는 경우를 OR-Expansion이라고 한다.

-----------------------------------------------------------------------------------------------
| 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 함수를 사용 job <> 'CLERK' 이거나 job is null 인 집합만 읽으려는 것이며, 이 함수는 조건이 false 이거나 null값일 때 true를 리턴한다.

• 힌트 종류
• USE_CONCAT : OR-Expansion 유도
• NO_EXPAND : 방지

• OR-Expansion 기능 작동여부
• 파라미터 : _no_or_expansion
• 사용 : alter session set "_no_or_expansion" = false;
• 파라미터는 기본값이 false이며 true로 설정하면 작동을 못하도록 하는 것이며,use_concat 힌트를 사용하더라도 OR-Expansion이 일어나지 않는다.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 275621146

------------------------------------------------------------------------------------------
| 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  | DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN             | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   FILTER                       |         |       |       |            |          |
|*  5 |    SORT JOIN                   |         |     6 |   228 |     3  (34)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP     |     6 |   228 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | EMP_N3  |     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)

• emp 테이블에 sal >= 2000 조건으로 먼저 읽고 조인한 dept 테이블을 액세스하는 단계에서 e.job = 'SALESMAN' or d.loc = 'CHICAGO' 조건이 필터링

• OR-Expansion 사용

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: 2632617833

-------------------------------------------------------------------------------------------------
| 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| DEPT           |     1 |    20 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | DEPT_N1        |     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 | 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| 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          | PK_DEPT        |     1 |       |     0   (0)| 00:00:01 |
|* 13 |    TABLE ACCESS BY INDEX ROWID | 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')) --교집합 방지를 위해 사용됨.

• loc='CHICAGO' 와 job='SALESMAN' 각기 다른 인덱스와 조인 순서를 가지고 실행되었다.
• 위쪽은 dept가 먼저 드라이빙되고 아래쪽은 emp가 먼저 드라이빙 되었다.

(3) 같은 컬럼에 대한 OR-Expansion

<쿼리1>
selec * from emp
where (depno=10 or deptno=30)
and ename = 'CLARK'

<쿼리2>
selec * from emp
where depno=10 in (10,30)
and ename = 'CLARK'

• 위에 두 쿼리는 내용적으로 같은 쿼리이기 때문에 OR-Expansion처리가 가능하다.

-----------------------------------------------------------------------------------------------
| 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)

• 실제로 9i까지는 OR 조건이나 IN-List도 OR-Expansion이 작동할 수 있었다.
• 10g부터는 IN-List Iterator 방식으로 처리된다.

-----------------------------------------------------------------------------------------------
| 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-Expansion으로 유도하려면 use_concat 힌트를 사용하여 유도 할 수 있지만,
IN-List Iteratoer에 비해 나은 점이 없다.

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

• 9i 까지는 OR조건이나 IN-List를 힌트를 이용해 OR-Expansion으로 유도하면 뒤쪽에 놓인 값이 항상 먼저 출력
• 10g 부터는 통계적으로 카디널리티가 작은 값이 먼저 출력
• 9i 처럼 뒤쪽을 먼저 출력하려고 하면 ordered_predicates 힌트를 사용

• 10g 버전이더라도 비교 연산자가 '=' 조건이 아닐 때는 일반적으로 use_concat 힌트로 OR-Expansion이 작동

select /*+ use_concat */*
  from emp
 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)
       filter(LNNVL("DEPTNO"=10))

(4). nvl/decode 조건식에 대한 OR-Expansion

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 || '%'

• deptno 변수값의 null 여부에 따라 위 또는 아래쪽 브렌치만 수행하는것이다.
• decode 함수를 사용하더라도 같은 처리가 일어난다

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 변수 값 입력에 따라 다른 인덱스를 사용한다는 사실
• null값을 입력했을 때 위쪽 브랜치 EMP_ENAME_IDX 사용 null값이 아닌 경우 아래쪽 EMP_DEPTNO_IDX 사용

이 기능을 제어하는 파라미터는 _or_expand_nvl_predicate이다.
옵티마이저가 스스로 그런 처리를 함으로써 편리해진 것은 사실이지만 nvl,decode를 여러 컬럼중 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어나기 때문에 옵션조건이 복잡할 때는 union all 분기를 해 줘야만 한다.