SQL 튜닝의 시작 (2013년)
목록
NVL 활용하기 0 0 99,999+

by 구루비스터디 NULL NVL [2018.07.14]

NVL 활용하기

  • 보통 "c3 like :b1 ||'%'" 와 같은 조회는 :B1 값에 따라 c3 = :b1 (:B1 IS NOT NULL) 이거나 c3 IS NOT NULL:B1 IS NULL)인 데이터를 조회하기 위해서 작성하는 경우가 많다.
  • 이와 같이 Bind 값에 따라 추출 결과가 달라져야 할 경우에는 일반적으로 NVL, LIKE,DECODE 를 이용하여 SQL 을 작성한다.


실행계획 분리하기



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 작성이 필요하다.


IS NULL 조회 개선하기


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.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3805

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입