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

DECODE




DECODE & CASE WHEN 이해 및 조건 문 처리하기

DECODE 함수 (이후 DECODE)는 SQL 내에서 IF - THEN - ELSE IF - END 로직을 사용 할 수 있도록 Oracle 에서 제공하는 함수이다.

DECODE( (column|expression), search1, result1 [,search2,result2] ...[,default] )

예) DECODE (9+1(COLUMN|EXPRESSION), 10(SEARCH1), '정답')
예) DECODE (9+1, 9, '정답1', 10, '정답2')
예) DECODE (9+1, 9, '정답1', 10, '정답2', '정답3')
예) DECODE( deptno , 30 , DECODE( sal , 2200 , 'DEPTNO=30 AND SAL=2200','DEPTNO=30 AND SAL<>2200' ) , 'DEPTNO <> 30' )

Decode 와 성능이슈

  • 샘플테이블
    테이블 데이터 건수는 약 1,000,000 ROWS
    - 판매일자는 20111201 ~ 20111210 일까지 존재하며 각 일자마다 10 만건씩 존재
    - 사원번호는 각 하루에 대해서 UNIQUE 한 값이다,
    - 부서번호는 10 개의 부서가 존재
    - TARGET, SALECNT 는 NOT NULL 제약 조건을 가진 컬럼이다.
    
    SQL> desc DECODE_T1
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------------------------------
     SALE_DT                                                        VARCHAR2(32) -- 판매일자
     EMPNO                                                          NUMBER       -- 사원번호
     DEPTNO                                                         NUMBER       -- 부서뽑기
     TARGET                                                NOT NULL NUMBER       -- 목표판매랑
     SALECNT                                               NOT NULL NUMBER       -- 실제판매랑
     SALE_DESC                                                      VARCHAR2(200)-- 목표와 실제 판매랑에 대한 상세내용
    
    
    
    SQL> CREATE INDEX IDX_DECODE_T1_01 ON DECODE_T1(SALE_DT);
    
    Index created.
    
    SQL> CREATE INDEX IDX_DECODE_T1_02 ON DECODE_T1(EMPNO);
    
    Index created.
    
    SQL>  EXEC dbms_stats.gather_table_stats('JIN', 'DECODE_T1') ;
    
    PL/SQL procedure successfully completed.
    
    
  • 로우 컬럼 변환 시 서브쿼리 사용예
    
    SELECT '2011/12/01' saledt_1201,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111201') AS target_1201,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111201') AS sale_1201,
    '2011/12/02' saledt_1202,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111202') AS target_1202,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111202') AS sale_1202,
    '2011/12/03' saledt_1203,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111203') AS target_1203,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111203') AS sale_1203,
    '2011/12/04' saledt_1204,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111204') AS target_1204,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111204') AS sale_1204,
    '2011/12/05' saledt_1205,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111205') AS target_1205,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111205') AS sale_1205,
    '2011/12/06' saledt_1206,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111206') AS target_1206,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111206') AS sale_1206,
    '2011/12/07' saledt_1207,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111207') AS target_1207,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111207') AS sale_1207,
    '2011/12/08' saledt_1208,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111208') AS target_1208,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111208') AS sale_1208,
    '2011/12/09' saledt_1209,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111209') AS target_1209,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111209') AS sale_1209,
    '2011/12/10' saledt_1210,
    (SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111210') AS target_1210,
    (SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111210') AS sale_1210
    FROM DUAL
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      1.59       8.54       9688    1234373          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                                                                                        
    total        4      1.59       8.55       9688    1234373          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 43
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=1025 pr=1021 pw=0 time=831394 us)
        100000     100000     100000   TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=1025 pr=1021 pw=0 time=964615 us cost=974 size=1244568 card=95736)
        100000     100000     100000    INDEX RANGE SCAN IDX_DECODE_T1_01 (cr=281 pr=278 pw=0 time=229060 us cost=270 size=0 card=95736)(object id 56120)
             1          1          1  SORT AGGREGATE (cr=1025 pr=0 pw=0 time=41446 us)
        100000     100000     100000   TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=1025 pr=0 pw=0 time=36491 us cost=974 size=1244568 card=95736)
        100000     100000     100000    INDEX RANGE SCAN IDX_DECODE_T1_01 (cr=281 pr=0 pw=0 time=14978 us cost=270 size=0 c
             1          1          1  SORT AGGREGATE (cr=100476 pr=1029 pw=0 time=836822 us)
         99722      99722      99722   TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=100476 pr=1029 pw=0 time=566883 us cost=974 size=1244568 card=95736)
         99722      99722      99722    INDEX RANGE SCAN IDX_DECODE_T1_01 (cr=99725 pr=279 pw=0 time=344408 us cost=270 size=0 card=95736)(object id 56120)
             1          1          1  SORT AGGREGATE (cr=100476 pr=0 pw=0 time=102259 us)
         99722      99722      99722   TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=100476 pr=0 pw=0 time=129272 us cost=974 size=1244568 card=95736)
         99722      99722      99722    INDEX RANGE SCAN IDX_DECODE_T1_01 (cr=99725 pr=0 pw=0 time=87204 us cost=270 size=0 card=95736)(object id 56120)
    
    ~~
             1          1          1  SORT AGGREGATE (cr=100564 pr=971 pw=0 time=782323 us)
         99866      99866      99866   TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=100564 pr=971 pw=0 time=862745 us cost=974 size=1244568 card=95736)
         99866      99866      99866    INDEX RANGE SCAN IDX_DECODE_T1_01 (cr=99869 pr=278 pw=0 time=283240 us cost=270 size=0 card=95736)(object id 56120)
             1          1          1  SORT AGGREGATE (cr=100564 pr=0 pw=0 time=101433 us)
    
    
  • 로우를 컬럼으로 변환 시 Decode 를 활용한 SQL 작성법
    SELECT '2011/12/01' saledt_1201,
      SUM(DECODE(sale_dt, '20111201',target, 0))  AS target_1201,
      SUM(DECODE(sale_dt, '20111201',salecnt, 0)) AS sale_1201,
      '2011/12/02' saledt_1202,
      SUM(DECODE(sale_dt, '20111202',target, 0))  AS target_1202,
      SUM(DECODE(sale_dt, '20111202',salecnt, 0)) AS sale_1202,
      '2011/12/03' saledt_1203,
      SUM(DECODE(sale_dt, '20111203',target, 0))  AS target_1203,
      SUM(DECODE(sale_dt, '20111203',salecnt, 0)) AS sale_1203,
      '2011/12/04' saledt_1204,
      SUM(DECODE(sale_dt, '20111204',target, 0))  AS target_1204,
      SUM(DECODE(sale_dt, '20111204',salecnt, 0)) AS sale_1204,
      '2011/12/05' saledt_1205,
      SUM(DECODE(sale_dt, '20111205',target, 0))  AS target_1205,
      SUM(DECODE(sale_dt, '20111205',salecnt, 0)) AS sale_1205,
      '2011/12/06' saledt_1206,
      SUM(DECODE(sale_dt, '20111206',target, 0))  AS target_1206,
      SUM(DECODE(sale_dt, '20111206',salecnt, 0)) AS sale_1206,
      '2011/12/07' saledt_1207,
      SUM(DECODE(sale_dt, '20111207',target, 0))  AS target_1207,
      SUM(DECODE(sale_dt, '20111207',salecnt, 0)) AS sale_1207,
      '2011/12/08' saledt_1208,
      SUM(DECODE(sale_dt, '20111208',target, 0))  AS target_1208,
      SUM(DECODE(sale_dt, '20111208',salecnt, 0)) AS sale_1208,
      '2011/12/09' saledt_1209,
      SUM(DECODE(sale_dt, '20111209',target, 0))  AS target_1209,
      SUM(DECODE(sale_dt, '20111209',salecnt, 0)) AS sale_1209,
      '2011/12/10' saledt_1210,
      SUM(DECODE(sale_dt, '20111210',target, 0))  AS target_1210,
      SUM(DECODE(sale_dt, '20111210',salecnt, 0)) AS sale_1210
    FROM DECODE_T1
    WHERE SALE_DT BETWEEN '20111201' AND '20111210'
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.81       1.25       7036       7039          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.81       1.25       7036       7039          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 43
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=7039 pr=7036 pw=0 time=1257969 us)
        957364     957364     957364   TABLE ACCESS STORAGE FULL DECODE_T1 (cr=7039 pr=7036 pw=0 time=331846 us cost=1926 size=16275188 card=957364)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      Disk file operations I/O                        1        0.00          0.00
      SQL*Net message to client                       2        0.00          0.00
      cell single block physical read                 4        0.00          0.00
      reliable message                                1        0.00          0.00
      enq: KO - fast object checkpoint                2        0.00          0.00
      cell smart table scan                          28        0.18          0.39
      SQL*Net message from client                     2       75.63         75.63
    ********************************************************************************
    
    alter session set cell_offload_processing=false
    ********************************************************************************
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.91       2.28       7032       7039          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.91       2.28       7032       7039          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 43
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=7039 pr=7032 pw=0 time=2280666 us)
        957364     957364     957364   TABLE ACCESS STORAGE FULL DECODE_T1 (cr=7039 pr=7032 pw=0 time=2926969 us cost=1926 size=16275188 card=957364)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      reliable message                                1        0.00          0.00
      enq: KO - fast object checkpoint                2        0.00          0.00
      direct path read                              446        0.01          1.26
      SQL*Net message from client                     2       10.23         10.23
    
    

앞에서 변경 전과 변경 후의 트레이스 결과를 확인해 보면, DECODE 함수를 이용해 처리할
경우 테이블을 한번만 읽고 처리해서 I/O 가 많이 개선되었지만 Elapsed Time 및 CPU Time 은 오히려 약간 증가하였다.



-- Index Clustering Factor 를 불리하게 하기 위해 TARGET 컬럼으로 정렬하여 데이터 입력 및 인덱스 생성
SQL> CREATE TABLE decode_temp AS SELECT * FROM decode_t1 ORDER BY target;

Table created.

SQL> CREATE INDEX idx_decode_temp ON decode_temp (sale_dt);

Index created.

SQL> CREATE INDEX idx_decode_temp_02 ON decode_temp (empno); 

Index created.

SQL> EXEC dbms_stats.gather_table_stats('JIN','decode_temp');

PL/SQL procedure successfully completed.

DECODE 사용하지 않는 경우(DECODE_TEMP)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.83       8.42       9694    1234373          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.83       8.42       9694    1234373          0           1

[SQL-2] DECODE 를 사용한 경우 (DECODE_TEMP)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.76       1.18       7036       7039          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.77       1.19       7036       7039          0           1

이와 같이 클러스터링 펙터가 않좋을 경우에는 더욱 서브쿼리형식 보다 Decode 경우 성능이 
좋게 나온다.
DECODE 사용시 생기는 비효율

CASE : DECODE 사용 시 꼭 필요하지 않은 Default 값을 지정하는 경우.

  • SUM 함수에서 숫자 + NULL 처리 할 때 NULL 만 리턴한다.
  • NULL 데이터를 가진 컬럼의 여러 로우를 SUM 할 때 NULL 데이터를 제외한 나머지 데이터에 대한 연산 처리만 수행한다.

정합성 문제로 NVL 사용 한다면 위치에 따라 연산 속도가 다르다. 주의가 필요하다.

  • NVL(SUM(DECODE(sale_dt, '20111204',target)), 0) 효율이 좋음
  • SUM(DECODE(sale_dt, '20111204',NVL(target, 0))) 효율이 나쁨
CASE 1
SQL> SELECT NVL(TO_CHAR(SUM( 1 + NULL )),'ISNULL') FROM DUAL ;

NVL(TO_CHAR(SUM(1+NULL)),'ISNULL')
----------------------------------------
ISNULL

CASE 2

SQL> WITH T1 AS (
  2  SELECT /*+ MATERIALIZE */
  3  1 AS NO
  4  FROM DUAL
  5  UNION ALL
  6  SELECT 2 AS NO
  7  FROM DUAL
  8  UNION ALL
  9  SELECT NULL
 10  FROM DUAL
 11  )
 12  SELECT SUM(NO) FROM T1 ;

   SUM(NO)
----------
         3

BIND 값에 따른 실행계획 분기

DECODE 의 잘못된 사용으로 인덱스를 사용할 수 없는 성능이슈와 해결 방법

  • BIND 값에 따른 실행계획 분기
    SELECT /*+ INDEX(A IDX_DECODE_T1_02) */ *
    FROM decode_t1 a
    WHERE empno = :b1 AND :b1 IS NOT NULL
    UNION ALL
    SELECT /*+ full(b) */ *
    FROM decode_t1 b
    WHERE empno = empno AND :b1 IS NULL
    

DECODE 를 Where 절에 사용될 때 나타날 수 있는 비효율

  • 즉 바인드 값에 따라 실행계획을 다르게 타야 될 경우 union all 형식으로 분기
    
    SELECT /*+ INDEX(A IDX_DECODE_T1_02) */
    *
    FROM DECODE_T1 a
    WHERE empno = DECODE( deptno, :b1, :b1, :b2)
    
    
    SELECT * FROM DECODE_T1 WHERE empno = DECODE( deptno, :b1, :b1, :b2 ) AND
    empno=:b1
    UNION ALL
    SELECT * FROM DECODE_T1 WHERE empno = DECODE( deptno, :b1, :b1, :b2 ) AND
    empno=:b2
    

문서정보

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