IS NOT NULL 조회에 대한 개선방법 찾기

• IS NOT NULL 조회는 SQL 구성이나 컬럼의 인덱스 구성정보에 따라 많은 성능편차가 발생하는데, 최소한 IS NOT NULL 로 조회하는 컬럼을 이용한 인덱스 스캔이 효율적이라면, ISNOT NULL 조회 조건을 인덱스 스캔이 가능한 조건으로 변경할 필요가 있다.
• IS NOTNULL 조건을 변경할 때는 컬럼의 데이터 타입에 따라 변경하는 방법이 달라지게 된다

-- CHAR[VARCHAR2]  : column > chr(0) [ or column > ' ']
-- DATE            : column > to_date('19000101','yyyymmdd')
-- NUMBER          : column >= 0 OR column <

-- char(varchar2) 타입의 컬럼의 IS NOTNULL
-- 기본적으로 is not null 은 인덱스를 사용하지 못함으로 다음과 같은 문자 타입은 
   가장 작은 보다 큰값 조회 하는 방식으로 인덱스를 탈수있다.

SQL> WITH not_null_t AS ( SELECT 'a' AS c1 FROM DUAL
  2  UNION ALL
  3  SELECT null FROM DUAL
  4  UNION ALL
  5  SELECT 'b' FROM DUAL
  6  UNION ALL
  7  SELECT '0' FROM DUAL )
  8  SELECT *
  9  FROM not_null_t
 10  WHERE c1 IS NOT NULL ;

C
-
a
b
0

SQL> WITH not_null_t AS ( SELECT 'a' AS c1 FROM DUAL
  2  UNION ALL
  3  SELECT null FROM DUAL
  4  UNION ALL
  5  SELECT 'b' FROM DUAL
  6  UNION ALL
  7  SELECT '0' FROM DUAL )SELECT *
  8  FROM not_null_t
  9  --WHERE c1 IS NOT NULL
 10  WHERE c1 > CHR(0) ;

C
-
a
b
0

-- DATE 타입의 컬럼의 IS NOTNULL

SQL> ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

Session altered.

Elapsed: 00:00:00.00
SQL> WITH not_null_t AS ( SELECT TO_DATE('20110101','yyyymmdd') AS c1 FROM DUAL
  2  UNION ALL
  3  SELECT NULL FROM DUAL
  4  UNION ALL
  5  SELECT TO_DATE('20111111','yyyymmdd') FROM DUAL )
  6  SELECT *
  7  FROM not_null_t
  8  WHERE c1 IS NOT NULL ;

C1
----------
2011-01-01
2011-11-11

Elapsed: 00:00:00.00
SQL> WITH not_null_t AS ( SELECT TO_DATE('20110101','yyyymmdd') as c1 FROM DUAL
  2  UNION ALL
  3  SELECT null FROM DUAL
  4  UNION ALL
  5  SELECT TO_DATE('20111111','yyyymmdd') FROM DUAL)
  6  SELECT *
  7  FROM not_null_t
  8  WHERE c1 > TO_DATE('19000101','yyyymmdd'); -- C1 컬럼에 존재할 수 없는 이전 값
 

-- Number 타입의 컬럼에 IS NOT NULL

-- NUMBER 타입인 컬럼의 IS NOT NULL 조회는 0 을 기점으로 하여 모든 데이터를 추출하도록 조건을 변경하면 된다.

SQL> WITH t1 AS ( SELECT -1 AS c1 FROM DUAL
  2  UNION ALL
  3  SELECT NULL FROM DUAL
  4  UNION ALL
  5  SELECT 1 FROM DUAL
  6  UNION ALL
  7  SELECT 1.2 FROM DUAL
  8  UNION ALL
  9  SELECT 0 FROM DUAL)
 10  SELECT *
 11  FROM t1
 12  WHERE c1 IS NOT NULL
 13  ;

        C1
----------
        -1
         1
       1.2
         0

SQL> WITH t1 AS ( SELECT -1 AS c1 FROM DUAL
  2  UNION ALL
  3  SELECT NULL FROM DUAL
  4  UNION ALL
  5  SELECT 1 FROM DUAL
  6  UNION ALL
  7  SELECT 1.2 FROM DUAL
  8  UNION ALL
  9  SELECT 0 FROM DUAL )
 10  SELECT *
 11  FROM t1
 12  WHERE c1 >= 0 OR c1 < 0 ;

        C1
----------
        -1
         1
       1.2
         0