컬럼의 데이터 타입 설정 의 중요성

잘못된 Bach Job 예시>

- SMS_YN컬럼을 CHAR(1) 그리고 NULLABLE로 생성
SQL> CREATE TABLE TN_SNS (SMS_NO NUMBER                   <-- SMS 전송번호
                         ,SMS_ID VARCHAR2(100) NOT NULL   <-- SMS 전송ID
                         ,SMS_YN CHAR(1));                <-- SMS 전송여부

- 전송해야 할 데이터를 테이블에 입력 할 때 SMS_YN을 NULL로 입력
SQL> INSERT INTO TN_SMS VALUES(1, 'Topsecret','');

SQL> COMMIT;

- SMS 전송해야 할 대상 추출
SQL> SELECT *
     FROM   TN_SMS
     WHERE  SMS_YN IS NULL;

- SMS 전송 후 전송 여부 업데이트
SQL> UPDATE TN_SMS 
     SET    SMS_YN = 'Y'
     WHERE  SMS_NO = 1;

SQL> COMMIT;

• 위 같이 Bach Job을 잡는다면 SMS를 전송해야 할 대상을 찾는 프로그램이 늘어날 수록 성능은 점점 악화
• 이유는 SMS_YN컬럼이 인덱스 컬럼(단일 인덱스)이라 할지라도 WHERE절에 IS NULL을 써주었기 때문에 인덱스를
  사용하지 못하고 Table Full Scan을 타기 때문이다.

IS NULL조회에 대한 개선방안

TEST Script

SQL> CREATE TABLE NULL_T5
       (C1 NUMBER
       ,C2 CHAR(1)
       ,C3 CHAR(1));

-- Data Insert
SQL> INSERT INTO NULL_T5
       SELECT LEVEL
             ,CHR(65+MOD(LEVEL,26))
             ,DECODE(MOD(LEVEL,10000),9999,NULL,'Y')
       FROM  dual
       CONNECT BY LEVEL <= 100000;

-- Key Commit
SQL> COMMIT;

-- Index
SQL> CREATE INDEX null_t5_idx_01 ON null_t5 (c3);

NULL_T5테이블은 총 100,000건인데 C3 컬럼이 NULL인 데이터는 총 10건으로 테이블 건수의 0.01%

SQL> SELECT COUNT(*) AS c3_null_cnt
           ,(COUNT(*)/100000)*100 AS c3_null_ratio
     FROM   null_t5
     WHERE  c3 IS NULL;

C3_NULL_CNT C3_NULL_RATIO                         
----------- -------------                         
         10           .01                         

실행계획

SQL> SELECT *
     FROM   NULL_T5
     WHERE  C3 IS NULL;

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     9 |   171 |    57   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| NULL_T5 |     9 |   171 |    57   (4)| 00:00:01 |
-----------------------------------------------------------------------------
                                                                             
Predicate Information (identified by operation id):                          
---------------------------------------------------                          
                                                                             
   1 - filter("C3" IS NULL)                                                  

• 히트율이 0.01%인 데이터를 Table Access Full을 하면서, 비효율적인 실행계획 발생
• 인덱스를 경유하면 좀 더 좋은 성능 발휘

개선방안

1. NVL처리와 Fucntion Based Index 생성
2. 컬럼속성변경(Default 설정)과 NULL 데이터 업데이트
3. 컬럼추가 및 인덱스 생성 후 WHERE절 변경

1) NVL처리와 Function Based Index 생성

책 참조
(잘못된 예시로 예상 됨)

2) 컬럼속성변경(Default 설정)과 NULL 데이터 업데이트

• 만일 현재 운영에서 운영하고 있는 중이라면, Default 변경 후, NULL값을 Default값으로 변경해 줘야 하는 작업을 해줘야 한다.
  하지만 개발 중이라면 OK

- Table의 컬럼 Default 변경
SQL> ALTER TABLE NULL_T5 MODIFY (C3 CHAR(1) DEFAULT 'N');

- NULL값을 Default값인 'N'으로 변경(운영 중일 경우)
SQL> UPDATE null_t5
     SET    c3    = 'N'
     WHERE  c3 IS NULL;

SQL> COMMIT;

- 실행계획 확인
SQL> SELECT *
     FROM   null_t5
     --WHERE  c3 is null
     WHERE  c3 = 'N';

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    10 |   190 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T5        |    10 |   190 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | NULL_T5_IDX_01 |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
                                                                                              
Predicate Information (identified by operation id):                                           
---------------------------------------------------                                           
                                                                                              
   2 - access("C3"='N')                                                                       

3) 컬럼추가 및 인덱스 생성 후 WHERE절 변경

• 결합인덱스의 특징
  : 선두컬럼이 NOT NULL 컬럼이면 뒤에 나오는 컬럼이 NULL이여도 인덱스는 경유

Ex) t_u1(A,B,C) 인덱스 A, B, C일 경우

Case1) 
SQL> SELECT *
     FROM   T
     WHERE  A=1
     AND    B=2;

Case2)
SQL> SELECT *
     FROM   T
     WHERE  A=1
     AND    C=2;

Case3)
SQL> SELECT *
     FROM   T
     WHERE  C=2;

• TEST

- C4 신규 컬럼 추가
: 기존 컬럼 중 null이 없고, 임의로 조건을 추가해도 값이 변하지 않는 컬럼이 이미 존재한다면, 꼭 신규 컬럼을 만들 필요 없음

SQL> ALTER TABLE null_t5 ADD (c4 CHAR(3) Default 'ALL');

- 기존 인덱스 변경 (C3 --> C4, C3)

SQL> DROP INDEX null_t5_idx_01;

SQL> CREATE INDEX null_t5_idx_01 ON null_t5 (c4, c3);

- 실행계획
SQL> SELECT *
     FROM   null_t5
     WHERE  c4 = 'ALL'
     AND    c3 IS NULL;

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     5 |   120 |   121   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| NULL_T5 |     5 |   120 |   121   (2)| 00:00:02 |
-----------------------------------------------------------------------------
                                                                             
Predicate Information (identified by operation id):                          
---------------------------------------------------                          
                                                                             
   1 - filter("C3" IS NULL AND "C4"='ALL')                                   

• 위 결과는 옵티마이져가 판단했을 때, 인덱스로 경유하는것 보다 FULL로 가는것이 더 좋다고 판단하여 실행계획이 위와 같이 나옴.

정리

• 테이블, 컬럼 설계 시, 용도에 맞게 설계 하는 것이 중요
• 만일 운영 중에 IS NULL을 검색해야 될 경우 결합 인덱스 생성 > 컬럼 속성 변경 > FBI인덱스 순으로 고려(내 생각임)