실행계획 분리하기
SQL> SELECT *
2 FROM NULL_T
3* WHERE c3 LIKE :b1 ||'%'
SQL> /
-- Like 사용하여 작성한 케이스
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 126K| 135 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| NULL_T | 5000 | 126K| 135 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | NULL_T_IDX_02 | 810 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3" LIKE :B1||'%')
filter("C3" LIKE :B1||'%')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3158 consistent gets
0 physical reads
0 redo size
134040 bytes sent via SQL*Net to client
2680 bytes received via SQL*Net from client
198 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2952 rows processed
-- NVL 이용하여 작성한 케이스
SQL> SELECT *
2 FROM NULL_T
3 WHERE c3 = NVL(:b1,c3);
C1 C2 C3 C4
---------- ----------- ---------- ----------
66664 09/JUL/1993 09-JUL-93 66664
Execution Plan
----------------------------------------------------------
Plan hash value: 4176338806
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90002 | 2285K| 141 (1)| 00:00:02 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS STORAGE FULL | NULL_T | 90000 | 2285K| 137 (1)| 00:00:02 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| NULL_T | 2 | 52 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | NULL_T_IDX_02 | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:B1 IS NULL)
3 - storage("C3" IS NOT NULL)
filter("C3" IS NOT NULL)
4 - filter(:B1 IS NOT NULL)
6 - access("C3"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
3 physical reads
0 redo size
737 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- DECODE 를 이용해서 작성한 케이스
SQL> set timing on
SQL> SELECT *
2 FROM null_t
3 WHERE c3 = DECODE(:b1,NULL,c3,:b1);
C1 C2 C3 C4
---------- ----------- ---------- ----------
66664 09/JUL/1993 09-JUL-93 66664
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4176338806
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90002 | 2285K| 141 (1)| 00:00:02 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS STORAGE FULL | NULL_T | 90000 | 2285K| 137 (1)| 00:00:02 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| NULL_T | 2 | 52 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | NULL_T_IDX_02 | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:B1 IS NULL)
3 - storage("C3" IS NOT NULL)
filter("C3" IS NOT NULL)
4 - filter(:B1 IS NOT NULL)
6 - access("C3"=:B1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
3 physical reads
0 redo size
737 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
이와 같이 :B1 의 Null 여부에 따라 실행계획이 분기되는데
LIKE 는 경우는 인덱스스캔으로 처리되므로 성능이 떨어진다.
그러러므로 조회패턴에 맞게 SQL 작성이 필요하다.
{panel}IS NULL 조회 개선하기{panel}
{code:sql}
SQL> SELECT *
2 FROM NULL_T
3 WHERE c3 IS NULL
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 253K| 137 (1)| 00:00:02 |
|* 1 | TABLE ACCESS STORAGE FULL| NULL_T | 10000 | 253K| 137 (1)| 00:00:02 |
------------------------------------------------------------------------------------
일반적으로 null 조건은 인덱스를 사용할 수 없어 fullscan 을 한다
이 경우 개선방안 중 하나가 Function BASE index 을 사용하는 방안이 있다.
Source
SQL> desc NULL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NOT NULL NUMBER
C2 DATE
C3 VARCHAR2(10)
C4 NUMBER
SQL> INSERT INTO NULL_T
SELECT LEVEL AS C1
,DECODE(MOD(LEVEL,10),0,NULL,TO_DATE('20110101','YYYYMMDD')+LEVEL) AS C2
,DECODE(MOD(LEVEL,10),0,NULL,TO_DATE('20110101','YYYYMMDD')+LEVEL) AS C3
,DECODE(MOD(LEVEL,10),0,NULL,LEVEL) AS C4
FROM DUAL
CONNECT BY LEVEL <= 100000 ;
COMMIT ;
SQL> CREATE INDEX NULL_T_IDX_01 ON NULL_T ( C1 ) ;
SQL> CREATE INDEX NULL_T_IDX_02 ON NULL_T ( C3 ) ;
SQL> CREATE INDEX NULL_T_IDX_03 ON NULL_T ( C2 ) ;
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'JIN',TABNAME=>'NULL_T',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
PL/SQL procedure successfully completed.
SQL>
SQL> CREATE TABLE NULL_T1
2 AS
3 SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+99999 AS C3
4 FROM DUAL
5 CONNECT BY LEVEL <= 100000 ;
Table created.
SQL> CREATE INDEX NULL_T1_IDX_01 ON NULL_T1 ( C2 ) ;
Index created.
SQL> EXEC dbms_stats.gather_table_stats(OWNNAME=>'JIN',TABNAME=>'NULL_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
PL/SQL procedure successfully completed.
SQL> CREATE TABLE NULL_T2
2 AS
3 SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2,
4 DECODE(MOD(LEVEL,10),0,NULL,MOD(LEVEL,10)) AS C3
5 FROM DUAL
6 CONNECT BY LEVEL <= 100000 ;
Table created.
SQL> CREATE INDEX NULL_T2_IDX_01 ON NULL_T2 ( C1, C2 ) ;
Index created.
SQL> EXEC dbms_stats.gather_table_stats(OWNNAME=>'JIN',TABNAME=>'NULL_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
PL/SQL procedure successfully completed.
SQL> CREATE TABLE NULL_T3
2 AS
3 SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2,
4 DECODE(MOD(LEVEL,100),0,NULL,MOD(LEVEL,100)) AS C3
5 FROM DUAL
6 CONNECT BY LEVEL <= 100000 ;
Table created.
SQL> CREATE INDEX NULL_T3_IDX_01 ON NULL_T3 ( C1, C2 ) ;
Index created.
SQL> EXEC dbms_stats.gather_table_stats(OWNNAME=>'JIN',TABNAME=>'NULL_T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
PL/SQL procedure successfully completed.
SQL> CREATE TABLE NULL_T5 (C1 NUMBER, C2 CHAR(1), C3 CHAR(1)) ;
Table created.
SQL> INSERT INTO NULL_T5
2 SELECT LEVEL, CHR(65+MOD(LEVEL,26)), DECODE(MOD(LEVEL,10000),9999,NULL,'Y')
3 FROM DUAL
4 CONNECT BY LEVEL <= 100000 ;
COMMIT ;
100000 rows created.
SQL>
Commit complete.
SQL> CREATE INDEX NULL_T5_IDX_01 ON NULL_T5 (C3) ;
Index created.
SQL> EXEC dbms_stats.gather_table_stats(OWNNAME=>'JIN',TABNAME=>'NULL_T5',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
PL/SQL procedure successfully completed.
SQL> CREATE TABLE NOT_NULL_T1 (C1 NUMBER, C2 CHAR(1), C3 CHAR(1)) ;
Table created.
SQL> INSERT INTO NOT_NULL_T1
2 SELECT LEVEL, CHR(65+MOD(LEVEL,26)), DECODE(MOD(LEVEL,10000),9999,'Y',NULL)
3 FROM DUAL
4 CONNECT BY LEVEL <= 500000 ;
500000 rows created.
SQL> EXEC dbms_stats.gather_table_stats(OWNNAME=>'JIN',TABNAME=>'NOT_NULL_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
PL/SQL procedure successfully completed.
SQL> CREATE TABLE NOT_NULL_T2 (C1 NUMBER, C2 CHAR(1), C3 CHAR(1)) ;
Table created.
SQL> INSERT INTO NOT_NULL_T2
2 SELECT LEVEL, CHR(65+MOD(LEVEL,26)), DECODE(MOD(LEVEL,10000),9999,'Y',NULL)
3 FROM DUAL
4 CONNECT BY LEVEL <= 500000 ;
COMMIT ;
500000 rows created.
SQL> EXEC dbms_stats.gather_table_stats(OWNNAME=>'JIN',TABNAME=>'NOT_NULL_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
PL/SQL procedure successfully completed.
SQL> CREATE TABLE null_t4
2 (
3 login_id NUMBER NOT NULL,
4 userid VARCHAR2(10) NOT NULL,
5 login_date VARCHAR2(8)
6 ) ;
Table created.
SQL> INSERT INTO NULL_T4 VALUES ( 1,'user1','20100101') ;
INSERT INTO NULL_T4 VALUES ( 2,'admin',NULL) ;
1 row created.
SQL>
1 row created.
SQL> INSERT INTO NULL_T4 VALUES ( 3,'user2','20100301') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES ( 6,'user1','20100601') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES ( 7,'admin',NULL) ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES ( 8,'user3','20100801') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (101,'user1','20100102') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (102,'admin',NULL) ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (103,'user2','20100302') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (106,'user1','20100602') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (107,'admin',NULL) ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (108,'user3','20100802') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (201,'user1','20100602') ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (202,'admin',NULL) ;
1 row created.
SQL> INSERT INTO NULL_T4 VALUES (203,'user2','20100802') ;
1 row created.
SQL> COMMIT ;
Commit complete.