OR Expansion을 사용한 부정형 조건 성능 개선

OR Expansion을 사용하면 부정형 조건의 성능을 개선할 수 있습니다. 글을 작성한 후 2018년에 유사한 내용의 글(인덱스와 부정형 조건)을 작성한 사실을 알았습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1, c2, c3)
AS
SELECT CEIL (ROWNUM / 100000), DECODE (MOD (ROWNUM, 100000), 0, 'N', 'Y'), LPAD ('X', 100, 'X')
  FROM XMLTABLE ('1 to 1000000');
  
CREATE INDEX t1_x1 ON t1 (c1, c2);

 

아래 쿼리는 c2 != 'Y' 조건으로 인해 인덱스 액세스 단계에서 266개의 블록 I/O가 발생합니다.

-- 2
SELECT *
  FROM t1
 WHERE c1 = 1
   AND c2 != 'Y';

---------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |      1 |     227 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |      1 |     227 |
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |      1 |      1 |     226 |
---------------------------------------------------------------------------------

 

아래 쿼리는 c2 != 'Y' 조건을 (c2 < 'Y' OR c2 > 'Y') 조건으로 변경하고 OR_EXPAND 힌트를 추가하여 인덱스 액세스 단계에서 7개의 블록 I/O가 발생합니다.

-- 3
SELECT /*+ OR_EXPAND */
       *
  FROM t1
 WHERE c1 = 1
   AND (c2 < 'Y' OR c2 > 'Y');

---------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |      1 |      1 |       8 |
|   1 |  VIEW                                 | VW_ORE_BA8ECEFB |      1 |      1 |       8 |
|   2 |   UNION-ALL                           |                 |      1 |      1 |       8 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |      1 |       5 |
|*  4 |     INDEX RANGE SCAN                  | T1_X1           |      1 |      1 |       4 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |      0 |       3 |
|*  6 |     INDEX RANGE SCAN                  | T1_X1           |      1 |      0 |       3 |
---------------------------------------------------------------------------------------------

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

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"=1 AND "C2"<'Y')
   6 - access("C1"=1 AND "C2">'Y' AND "C2" IS NOT NULL)
       filter(LNNVL("C2"<'Y'))