인덱스와 부정형 조건

부정형 조건은 인덱스를 사용할 수 없다. 부정형 조건에 사용된 값의 분포도가 큰 경우라면 부정형 조건을 OR 조건으로 변경함으로써 인덱스를 사용할 수 있다. 관련 내용을 살펴보자.   

테스트를 위해 아래와 같이 테이블을 생성하자. c2 열에서 B는 분포도 98%(=(9800/10000)*100)를 차지한다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1
     , CASE
           WHEN ROWNUM <= 100 THEN 'A'
           WHEN ROWNUM <= 200 THEN 'C'
           ELSE 'B'
       END AS c2
  FROM XMLTABLE ('1 to 10000');

CREATE INDEX t1_x1 ON t1 (c2);

아래 쿼리는 c2가 B가 아닌 행을 조회한다. 부정형 조건을 사용했기 때문에 인덱스를 사용할 수 없다.

-- 2
SELECT /*+ INDEX(T1) */ COUNT (c1) FROM t1 WHERE c2 <> 'B';

-----------------------------------------------------------------
| Id  | Operation                    | Name  | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      21 |
|   1 |  SORT AGGREGATE              |       |      1 |      21 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    200 |      21 |
|*  3 |    INDEX FULL SCAN           | T1_X1 |    200 |      20 |
-----------------------------------------------------------------

아래 쿼리처럼 OR 조건을 사용하면 c2가 B가 아닌 행을 조회할 수 있다. 내부적으로 USE_CONCAT 힌트가 사용된다.

-- 3 : 11.2
SELECT /*+ INDEX(T1) */ COUNT (c1) FROM t1 WHERE (c2 < 'B' OR c2 > 'B');

------------------------------------------------------------------
| Id  | Operation                     | Name  | A-Rows | Buffers |
------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |       7 |
|   1 |  SORT AGGREGATE               |       |      1 |       7 |
|   2 |   CONCATENATION               |       |    200 |       7 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |    100 |       4 |
|*  4 |     INDEX RANGE SCAN          | T1_X1 |    100 |       3 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1    |    100 |       3 |
|*  6 |     INDEX RANGE SCAN          | T1_X1 |    100 |       2 |
------------------------------------------------------------------

Outline Data
-------------
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))

12.1 이후 버전은 OR_EXPAND 힌트가 사용된다.

-- 4 : 12.1
SELECT /*+ INDEX(T1) */ COUNT (c1) FROM t1 WHERE (c2 < 'B' OR c2 > 'B');

------------------------------------------------------------------
| Id  | Operation                              | Name            |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |
|   1 |  SORT AGGREGATE                        |                 |
|   2 |   VIEW                                 | VW_ORE_BA8ECEFB |
|   3 |    UNION-ALL                           |                 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1              |
|*  5 |      INDEX RANGE SCAN                  | T1_X1           |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1              |
|*  7 |      INDEX RANGE SCAN                  | T1_X1           |
------------------------------------------------------------------

Outline Data
-------------
      OR_EXPAND(@"SEL$1" (1) (2))