SQL 튜닝의 시작 (2013년)
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)
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3808
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.