by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




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

잘못된 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인덱스 순으로 고려(내 생각임)

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.