[ORACLE] OR-expansion과 USE_CONCAT, NO_EXPAND 힌트

OR-expansion 이란?

OR-expansion은 조건절에  OR 조건이나 INLISTS 조건이 있을 때 내부적으로 여러 개의 쿼리로 나누어 처리하는 기능이다.

 

 

OR-expansion 예시

예를들어 아래와 같은 쿼리가 있다.

deptno, salary 에 대한 인덱스가 있다고 가정한다.

select * from emp
where
deptno = 20
or salary > 5000;

 

 

- OR-expansinon 이 발생하지 않은 경우

OR-expansinon 이 발생하지 않은 경우, 하나의 조건절에서 여러 개의 인덱스가 사용될 수 없기 때문에 아래 실행계획과 같이 OR 조건이 full scan으로 처리된다.

 

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|
|*  1 |  TABLE ACCESS FULL | EMP  |    14 |   448 |     3   (0)|
-------------------------------------------------------------------

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

   1 - filter("DEPTNO"=20 OR "SALARY">5000)

 

 

 

- OR-expansinon 이 발생한 경우

OR-expansion 이 발생한다면 해당 쿼리는 내부적으로 아래 쿼리와 같이 처리된다.

 

select * from employees
where
deptno = 20

union all

select * from employees
where
salary > 5000;

 

 

OR-expansion 으로 처리되었을 때, 아래 실행계획과 같이 각 조건에 대하여 인덱스가 적용될 수 있다.

 

-------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  |  Cost (%CPU)| Pstart|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |             |       |
|   1 |  CONCATENATION               |           |       |             |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     5 |      2   (0)|       |
|*  3 |    INDEX RANGE SCAN          | IDX_DEPTNO|     5 |      1   (0)|       |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     5 |      2   (0)|       |
|*  5 |    INDEX RANGE SCAN          | IDX_SALARY|     5 |      1   (0)|       |
-------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=20)
   5 - access("SALARY">5000)

 

 

---

 

 

OR-expansion 을 제어하는 힌트

USE_CONCAT

select /*+ USE_CONCAT */ *
from emp
where
deptno = 20
or salary > 5000;

 

OR-expansion 을 유도.

 

조건절에 OR 조건이나 INLISTS 조건이 있을 때, 쿼리가 확장된 후 CONCATENATION를 사용하여 결합되도록 유도한다.

 

OR로 묶인 조건들에 대하여 알맞은 인덱스가 있는 경우, USE_CONCAT 힌트를 사용하여 OR-expansion 을 유도하고 개별적으로 힌트를 탈 수 있도록 하는 것이 합리적일 수 있다.

 

 

 

NO_EXPAND

select /*+ NO_EXPAND */ *
from emp
where
deptno = 20
or salary > 5000;

 

OR-expansion 방지.

 

조건절에 OR 조건이나 INLISTS 조건이 있을 때, 쿼리가 확장되지 않고 그대로 실행되도록 유도한다.

 

OR로 묶인 조건들에 대하여 조건에 알맞은 인덱스가 없는 경우,  NO_EXPAND를 사용하여 OR-expansion을 방지하고 full table scan이나 index full scan을 유도하는 것이 더 합리적일 수 있다.