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