IS NULL OR 방식 옵션 조건 처리

IS NULL OR 방식 옵션 조건 처리는 인덱스에서 OR 조건을 사용할 수 없어 성능 저하가 발생할 수 있다. 이번 글에서 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1, ROWNUM AS c2, LPAD ('X', 4000, 'X') AS c4
  FROM XMLTABLE ('1 to 10000');

CREATE INDEX t1_x1 ON t1 (c1, c2);

아래는 v2 바인드 변수를 선택 조건 처리한 쿼리다. 테이블 랜덤 액세스에 의해 과도한 블록 I/O가 발생한 것을 확인할 수 있다.

-- 2
VAR v1 NUMBER;
VAR v2 NUMBER;

EXEC :v1 := 0;
EXEC :v2 := 1;

SELECT *
  FROM t1
 WHERE c1 > :v1
   AND (    :v2 IS NULL
        OR (:v2 IS NOT NULL AND c2 = :v2));

-------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |      1 |   10029 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |   10029 | -- !
|*  2 |   INDEX RANGE SCAN          | T1_X1 |      1 |  10000 |      29 |
-------------------------------------------------------------------------

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

   1 - filter((:V2 IS NULL OR (:V2 IS NOT NULL AND "C2"=:V2)))
   2 - access("C1">:V1 AND "C1" IS NOT NULL)

다양한 해법이 있지만 가장 간단한 방법은 아래 쿼리처럼 CASE 표현식을 사용하는 것이다. 블록 I/O가 감소한 것을 확인할 수 있다. IS NULL OR 방식 자체가 바람직한 옵션 조건 처리 방식이 아니다. CASE 표현식은 해당 방식에 대한 성능 개선이 필요한 경우에만 제한적으로 사용해야 한다.

-- 3
SELECT *
  FROM t1
 WHERE c1 > :v1
   AND CASE WHEN
       (    :v2 IS NULL
        OR (:v2 IS NOT NULL AND c2 = :v2)) THEN 1 END = 1;

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

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

   2 - access("C1">:V1 AND "C1" IS NOT NULL)
       filter(CASE  WHEN (:V2 IS NULL OR (:V2 IS NOT NULL AND "C2"=:V2)) THEN 1 END =1)