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

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




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

테이블: SMS 전송 (Table Name: TN_SMS)
컬럼: SMS 전송 체크 (Column Name: SMS_YN)

  • TN_SMS 테이블의 컬럼 중 SMS 의 전송을 체크하는 컬럼(SMS_YN)을 Nullable 로 정의하고, SMS 전송이 되지 않은 데이터에는 NULL 을, 전송이 완료된 데이터에는 Y 로 업데이트하는 프로그램이 있다고 가정
- SMS_YN 컬럼을 CHAR(1) 그리고 NULLABLE 로 생성

SQL> CREATE TABLE TN_SMS (SMS_NO NUMBER, SMS_ID VARCHAR2(100) NOT NULL, SMS_YN CHAR(1)) ;

- 전송해야 할 데이터를 테이블에 입력할 때 SMS_YN(전송여부)를 NULL 로 입력

SQL> INSERT INTO TN_SMS VALUES (1, 'SHPARK', '') ;

1 row created.

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

- SMS 전송해야 할 대상 추출

SQL> SELECT * FROM TN_SMS WHERE SMS_YN IS NULL ;

    SMS_NO SMS_ID                                                                                               S
---------- ---------------------------------------------------------------------------------------------------- -
         1 SHPARK

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

1 row updated.

Elapsed: 00:00:00.01

COMMIT;
 -- 이런 케이스라면 점점 성능이 느려지겠죠.

Sample

  • NULL_T5 테이블은 총 100,000 건을 가지고 있다. 데이터 중 C3 컬럼이 NULL 인 데이터는 총 10 건으로 전체 테이블 건수의 0.01%이다.
    
    SQL> SELECT COUNT(*) AS c3_null_cnt,(count(*)/100000)*100 AS c3_null_ratio
      2  FROM null_T5
      3  WHERE c3 is null ;
    
    C3_NULL_CNT C3_NULL_RATIO
    ----------- -------------
             10           .01
    

개선방안 1. NVL 처리와 FUNCTION BASED INDEX 생성

SQL> CREATE INDEX null_t5_idx_02 ON null_t5 ( NVL(c3,'ISNULL') )
  2  ;

Index created.

Elapsed: 00:00:00.37

SQL> SELECT *
  2  FROM null_t5
  3  --WHERE c3 IS NULL
  4  WHERE NVL(c3,'ISNULL') = 'ISNULL';

        C1 C C
---------- - -
      9999 P
     19999 F
     29999 V
     39999 L
     49999 B
     59999 R
     99999 D
     89999 N
     69999 H
     79999 X

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1544188370

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |  1000 |  9000 |    67   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T5        |  1000 |  9000 |    67   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | NULL_T5_IDX_02 |   400 |       |    91   (0)| 00:00:02 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NVL("C3",'ISNULL')='ISNULL')

개선방안 2. 컬럼 속성 변경(DEFAULT 설정)과 NULL 데이터 업데이트

- C3 컬럼에 DEFAULT VALUE 설정하기 위해 ALTER TABLE 수행
ALTER TABLE null_t5 MODIFY (c3 char(1) default 'N') ;

SQL> ALTER TABLE null_t5 MODIFY (c3 char(1) default 'N') ;

Table altered.

Elapsed: 00:00:00.07
SQL> UPDATE null_t5
  2  SET c3 = 'N'
  3  WHERE c3 IS NULL ;

10 rows updated.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3671621737

--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT           |         |    10 |    20 |    69   (2)| 00:00:01 |
|   1 |  UPDATE                    | NULL_T5 |       |       |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| NULL_T5 |    10 |    20 |    69   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

SQL> COMMIT ;

Commit complete.

Elapsed: 00:00:00.01
SQL> SELECT *
  2  FROM null_t5
  3  --WHERE c3 IS NULL
  4  WHERE c3 = 'N'
  5  ;

        C1 C C
---------- - -
      9999 P N
     19999 F N
     29999 V N
     39999 L N
     49999 B N
     59999 R N
     99999 D N
     89999 N N
     69999 H N
     79999 X N

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3090382039


----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T5        |     1 |     9 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | NULL_T5_IDX_01 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

개선방안 3. 컬럼 추가 및 인덱스 생성 후 WHERE 절 변경

인덱스 구성 (단일 컬럼 인덱스 : 컬럼 A)     
  컬럼 A 의 값: NULL (NULL 값이므로 인덱스에 저장되지 않는다.)
인덱스 구성 (결합 인덱스 : 컬럼 B, 컬럼 A)   컬럼 B 와 A 의 값:
   1, NULL (B 컬럼까지 NULL 이었다면, 인덱스에 저장되지 않지만 B 값이 NULL 이 아닌 값을 가지고 있으므로
    인덱스에 저장되어야 한다. 즉, A 는 똑같이 NULL 이지만 B 와 결합인덱스가 될 경우 B 컬럼의 값이 NULL
    이 없다면 A 는 NULL 이든 NULL 이 아니든 상관없이 항상 인덱스에 저장이 된다.)

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

Table altered.

Elapsed: 00:00:06.08
SQL> DROP INDEX null_t5_idx_01 ;

Index dropped.

Elapsed: 00:00:00.07
SQL> CREATE INDEX null_t5_idx_01 ON null_t5 (c4, c3) ;

Index created.

Elapsed: 00:00:00.26
SQL> SELECT *
  2  FROM null_t5
  3  WHERE c3 IS NULL
  4  AND c4 = 'ALL' ;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3090382039

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |     9 |    67   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T5        |     1 |     9 |    67   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | NULL_T5_IDX_01 |     1 |       |   118   (0)| 00:00:02 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C4"='ALL' AND "C3" IS NULL)



문서정보

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