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