InList / Concatnation / Range Scan Control 하기

프로젝트를 하다보면 결합인덱스의 2번째 혹은 3번째 컬럼에 IN 조건을 많이 사용하는것이 관찰된다.
오늘은 결합인덱스 에서 IN 을 사용한경우 나타날수있는3가지 경우(InList, Concatnation, Range Scan)에
대하여 알아 보겠다.

버젼 11.1.0.6
인덱스 : EMP_JOB_MGR_SAL_IDX (JOB_ID, MANAGER_ID, SALARY)  

아래는 인덱스의 첫번째 컬럼이 = 조건으로 들어오고 두번째 컬럼이 IN 변수조건으로 들어 왔을때의
전통적인 방식의 PLAN 이다.

EXPLAIN PLAN FOR
SELECT /*+ RULE */
             a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2);

select * from table(dbms_xplan.display);

------------------------------------------------------------
| Id  | Operation                    | Name                |
------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |
|   1 |  CONCATENATION               |                     |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  5 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
------------------------------------------------------------
 
별로 특별할것이 없는 전통적인 방식의 CONCATENATION Plan 이 나왔다.
그렇다면 Rule 힌트를 빼면 어떻게 되는겠는가?
CONCATENATION Plan 대신에 InList Plan 을 기대하는가?
아래의 Plan 을 보자.

EXPLAIN PLAN FOR
SELECT a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2) ;

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

InList Plan 이 나오지 않는다.
어떻게 된것인가?
혹자는 NO_EXPAND 힌트를 사용하라고 이야기 하지만 그것은 CONCATENATION Plan 을 방지하는 힌트일뿐
InList Plan으로 유도하지 않는다.

EXPLAIN PLAN FOR
SELECT /*+ NO_EXPAND */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2);

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

예상과는 다르게 Range Scan 이 나오고 IN 절은 Filter 조건으로 되고말았다.
그렇다면 이런경우 도데체 어떻게 InList Plan 으로 유도 한단 말인가?
이런경우에 사용할수 있는 힌트가 바로 NUM_INDEX_KEYS 이다.
아래의 SQL 을보자.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2)
   ;

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2)))

깔끔하게 InList Plan 으로 유도 되었다.
그렇다면 NUM_INDEX_KEYS 힌트의 마지막 인자인 숫자 2는 무엇을 의미하는가?
결합 인덱스의 2번째 컬럼(MANAGER_ID) 까지는 InList Plan 으로 풀리는 것을 의미한다.
거꾸로 이야기하면 NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) 로 힌트를 주면 인덱스의 3번째
컬럼인 SALARY 조건에 IN 조건을 주어도 InList Plan 으로 풀리지 않는다.
아래의 SQL및 Plan 을 보고 InList Plan 이 나왔다고 생각하면 오산이다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)))
       filter("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR
              "SALARY"=TO_NUMBER(:V_SAL3))

위 Plan 에서의 InList 는 Salary 조건과는 상관없이 Job_id 와 Manager_id  두가지 조건만으로 InList 가
나온것이다.
Salary 조건은 역시 Filter 로 빠진것을 알수 있다.
아래처럼 3으로 바꾼다면 인덱스의 3번째컬럼 까지 InList 로 풀리는걸 볼수 있다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 3) */
        a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)) AND
              ("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR "SALARY"=TO_NUMBER(:V_SAL3)))

하지만 manager_id 종류가 많지않은 경우에는 Range Scan이 더 나은 성능을 보이는 경우가 많으므로
무조건 적용해서는 안된다.
오늘 사용한 Rule 힌트나 NUM_INDEX_KEYS 힌트는 Production 시스템에서는 사용하면 안된다.
사용하려면 deprecate 된 힌트나 undocument 힌트를 사용한 SQL 만 따로 목록을 만들어서 관리해야 할것이다.

PS: 테스트 환경은 11g 에서 테스트 됬지만 10g 에서 테스트 해도 동일한 결과를 얻을수 있다.