• IS NOT NULL조회는 SQL구성이나 컬럼의 인덱스 구성정보에 따라 성능편차 발생
• IS NOT NULL로 조회하는 컬럼을 이용한 인덱스 스캔이 효율적이라면, IS NOT NULL조회 조건을 인덱스 스캔이 가능한 조건으로 변경
• 컬럼의 데이터 타입에 따라 변경하는 방법 결정

다양한 IS NOT NULL처리와 SQL 성능 문제

CHAR[VARCHAR2] : column > chr(0) [or colum > '']
DATE           : column > to_date('19831116','YYYYMMDD')
NUMBER         : column >= OR column < 0   

CHAR(VARCHAR2)타입인 컬럼의 IS NOT NULL

• IS NOT NULL은 컬럼에 NULL이 아닌 데이터만 추출
• CHAR타입인 컬럼에 대한 IS NOT NULL조회는 인덱스 사용 불가

IS NOT NULL 조회

Case1>
SQL> WITH not_null_t AS (SELECT 'a' AS c1 FROM dual
                         UNION ALL
                         SELECT NULL FROM dual
                         UNION ALL
                         SELECT 'b' FROM dual
                         UNION ALL
                         SELECT 'o' FROM dual)
SQL> SELECT *
     FROM   not_null_t
     WHERE  c1 IS NOT NULL;

c1
--------------
a
b
o

- Case2>
SQL> WITH not_null_t AS (SELECT 'a' AS c1 FROM dual
                         UNION ALL
                         SELECT NULL FROM dual
                         UNION ALL
                         SELECT 'b' FROM dual
                         UNION ALL
                         SELECT 'o' FROM dual)
SQL> SELECT *
     FROM   not_null_t
     --WHERE  c1 IS NOT NULL
     WHERE  c1 > CHR(0); --> 또는 c1 > ''

c1
--------------
a
b
o

DATE타입인 컬럼의 IS NOT NULL

• 인덱스 스캔 가능한 구문 방법은 컬럼에 존재할 수 없는 가장 작은 값을 이용하여 조회하는 것

IS NOT NULL조회

Case1>
SQL> ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

SQL> WITH not_null_t AS (SELECT to_date('20110101','YYYYMMDD') AS c1 FROM dual
                         UNION ALL
                         SELECT NULL FROM dual
                         UNION ALL
                         SELECT to_date('20111111','YYYYMMDD') FROM dual)
SQL> SELECT *
     FROM   not_null_t
     WHERE  c1 IS NOT NULL;
     
C1
-----------------
2011-01-01
2011-11-11

Case2>
SQL> ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

SQL> WITH not_null_t AS (SELECT to_date('20110101','YYYYMMDD') AS c1 FROM dual
                         UNION ALL
                         SELECT NULL FROM dual
                         UNION ALL
                         SELECT to_date('20111111','YYYYMMDD') FROM dual)
SQL> SELECT *
     FROM   not_null_t
     --WHERE  c1 IS NOT NULL
     WHERE  c1 > to_date('19000101','YYYYMMDD'); <-- c1컬럼에 존재 할 수 없는 이전 값
     
C1
-----------------
2011-01-01
2011-11-11

NUMBER타입인 컬럼의 IS NOT NULL

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

Case1>
SQL> WITH t1 AS (SELECT -1 AS c1 FROM dual
                 UNION ALL
                 SELECT NULL FROM dual
                 UNION ALL
                 SELECT 1 FROM dual
                 UNION ALL
                 SELECT 1.2 FROM dual
                 UNION ALL
                 SELECT 0 FROM dual)
SQL> SELECT *
     FROM   t1
     WHERE  c1 IS NOT NULL;

   c1
-----
   -1
    1
  1.2
    0

Case2>
SQL> WITH t1 AS (SELECT -1 AS c1 FROM dual
                 UNION ALL
                 SELECT NULL FROM dual
                 UNION ALL
                 SELECT 1 FROM dual
                 UNION ALL
                 SELECT 1.2 FROM dual
                 UNION ALL
                 SELECT 0 FROM dual)
SQL> SELECT *
     FROM   t1
     --WHERE  c1 IS NOT NULL;
     WHERE  c1 >= 0 OR C1 < 0;

   c1
-----
   -1
    1
  1.2
    0

조인조건 시 IS NOT NULL 활용하기

• 테이블간 조인을 수행 할 때 드라이빙 테이블과 필터 테이블의 컬럼과 컬럼을 비교하여 처리하는데, NULL을 포함하지 않는 값으로 비교하게 되므로
  기본적으로 조인 연결컬럼은 IS NOT NULL조건을 가짐

단일 테이블의 컬럼 비교와 IS NOT NULL

SQL> SELECT *
     FROM   not_null_t1
     WHERE  c3 = c3;

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |    26 |   494 |  1272   (1)| 00:00:16 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NOT_NULL_T1        |    26 |   494 |  1272   (1)| 00:00:16 |
|*  2 |   INDEX FULL SCAN           | NOT_NULL_T1_IDX_01 | 24656 |       |  1228   (1)| 00:00:15 |
--------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
                                                                                                  
   2 - filter("C3" IS NOT NULL)  <-- c3=c3 은 c3 IS NOT NULL조건을 포함!!

테이블 조인과 IS NOT NULL

SQL> SELECT t1.*
           ,t2.*
     FROM   not_null_t1 t1
           ,not_null_t2 t2
     WHERE  t1.c3       IS NOT NULL
     AND    t1.c2       = t2.c2
     AND    t1.c3       = t2.c3

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |   506K|    18M|  1470   (2)| 00:00:18 |
|*  1 |  HASH JOIN                   |                    |   506K|    18M|  1470   (2)| 00:00:18 |
|   2 |   TABLE ACCESS BY INDEX ROWID| NOT_NULL_T1        |    26 |   494 |  1229   (1)| 00:00:15 |
|*  3 |    INDEX FULL SCAN           | NOT_NULL_T1_IDX_01 |    26 |       |  1228   (1)| 00:00:15 |
|*  4 |   TABLE ACCESS FULL          | NOT_NULL_T2        |   501K|  9310K|   236   (5)| 00:00:03 |
---------------------------------------------------------------------------------------------------
                                                                                                   
Predicate Information (identified by operation id):                                                
---------------------------------------------------                                                
                                                                                                   
   1 - access("T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")                                         
   3 - filter("T1"."C3" IS NOT NULL)                                                               
   4 - filter("T2"."C3" IS NOT NULL)

• <AND t1.c3 = t2.c3> 이 조건을 처리하면서 각각의 IS NOT NULL 조회 조건을 포함
  만약, T2.C3컬럼이 NULL이 아닌 데이터가 적다면 T2.C3컬럼에 신규 인덱스를 생성하고 인덱스 가능하도록 조건 변경

- NOT_NULL_T2.C3에 신규 인덱스 생성
SQL> CREATE INDEX not_null_t2_idx_01 ON not_null_t2 (c3);

- 실행계획
SQL> SELECT t1.*
           ,t2.*
     FROM   not_null_t1 t1
           ,not_null_t2 t2
     WHERE  t1.c3       > CHR(0)
     AND    t1.c2       = t2.c2
     AND    t1.c3       = t2.c3
     AND    t1.c3       > CHR(0);

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |   109 |  4142 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                   |                    |   109 |  4142 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| NOT_NULL_T1        |    50 |   950 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | NOT_NULL_T1_IDX_01 |    50 |       |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| NOT_NULL_T2        |    50 |   950 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | NOT_NULL_T2_IDX_01 |    50 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
                                                                                                   
Predicate Information (identified by operation id):                                                
---------------------------------------------------                                                
                                                                                                   
   1 - access("T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")                                         
   3 - access("T1"."C3">' ' AND "T1"."C3" IS NOT NULL)                                             
   5 - access("T2"."C3">' ')                                                                       

정리

• IS NOT NULL을 조건절로 받아 올때 인덱스를 경유하지 못하기 때문에 각 데이터 타입에 따라서 최소 값을 정해주고 Range방식으로 조건절을 바꾸어 준다면 효율 적이다.