IS NULL OR 방식 옵션 조건 처리

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

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

1 2 3 4 5 6 7 8

-- 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가 발생한 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-- 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 표현식은 해당 방식에 대한 성능 개선이 필요한 경우에만 제한적으로 사용해야 한다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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