ORACLE DB SQL 쿼리 튜닝을 통한 처리속도 저하 요인을 파악하고 개선한 사항 참조하세요[DB 튜닝]
1. SQL 쿼리 속도 저하 요인
RPT_DAILY 테이블 3회 Access
Index 컬럼(IDATE) SUBSTR 함수사용으로 변형되어 3회 Full Scan
NOT NULL 컬럼 VALUE에 NVL 함수 2회 사용으로 별도 함수계산
2. SQL 쿼리 속도 개선 사항
ROW_NUMBER 사용으로 2회 Access
Index 컬럼(IDATE) 사용으로 2회 Index Scan
NVL 함수 사용 억제
3. SQL 쿼리
3.1 튜닝전 일부 쿼리
SELECT ROWNUM, 'S_' || IP, CNT FROM
FROM
RPT_DAILY
<§>
SUBSTR(IDATE, 1, 6) = SUBSTR(200801, 1, 6)
<§>
UNION ALL
(SELECT
NVL(SUM(VALUE), 0)CNT
FROM
RPT_DAILY
WHERE
SUBSTR(IDATE, 1, 6) = SUBSTR(200801, 1, 6)
AND NOT
SELECT NVL(SUM(CNT), 0) CNT FROM (
SELECT KEY IP, SUM(VALUE) CNT
FROM
RPT_DAILY
WHERE SUBSTR(IDATE, 1, 6) = SUBSTR(200801, 1, 6)
AND NOT
3.2 튜닝후 일부 쿼리
SELECT ROWNUM, 'S_' || IP, CNT FROM
FROM
RPT_DAILY
IDATE LIKE '200801'||'%'
UNION ALL
SELECT 999, '기타', SUM(H_SUM_CNT) - SUM(SUM_SIP_CNT)
FROM
(SELECT A.IP, A.SIP_CNT, A.H_SUM_CNT, ROW_NUMBER() OVER (PARTITION BY B.NO ORDER BY A.SIP_CNT DESC) AS RNUM
FROM
(SELECT CASE WHEN L2 = 'SIP' THEN KEY
WHEN L2 = 'H_SUM' THEN '기타'
END IP,
SUM(CASE WHEN L2 = 'SIP' THEN VALUE
END) SIP_CNT,
SUM(CASE WHEN L2 = 'H_SUM' THEN VALUE
END) H_SUM_CNT
FROM
RPT_DAILY
AND IDATE LIKE '200801'||'%'
(SELECT LEVEL NO
FROM DUAL
CONNECT BY LEVEL <=1) B
4. 실행 PLAN
4.1 튜닝전
Rows Execution Plan
------- ------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE
10 VIEW
10 SORT (ORDER BY STOPKEY)
317 SORT (GROUP BY)
611 TABLE ACCESS (FULL) OF 'RPT_DAILY'
1 NESTED LOOPS
1 VIEW
1 SORT (AGGREGATE)
10 COUNT (STOPKEY)
10 VIEW
10 SORT (ORDER BY STOPKEY)
317 SORT (GROUP BY)
611 TABLE ACCESS (FULL) OF 'RPT_DAILY'
1 VIEW
1 FILTER
1 SORT (AGGREGATE)
1493 TABLE ACCESS (FULL) OF 'RPT_DAILY'
4.2 튜닝 후
Rows Execution Plan
------- ------------------------------------------ 0 SELECT STATEMENT GOAL: CHOOSE
10 VIEW
10 SORT (ORDER BY STOPKEY)
317 SORT (GROUP BY)
611 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_DAILY'
400607 INDEX (RANGE SCAN) OF 'IDX_RPT_DAILY_IDATE'
(NON-UNIQUE)
11 VIEW
318 WINDOW (SORT PUSHED RANK)
318 NESTED LOOPS
1 VIEW
1 CONNECT BY (WITHOUT FILTERING)
1 TABLE ACCESS (FULL) OF 'DUAL'
318 VIEW
318 FILTER
319 SORT (GROUP BY)
2724 TABLE ACCESS (BY INDEX ROWID) OF
'RPT_DAILY'
400607 INDEX (RANGE SCAN) OF
'IDX_RPT_DAILY_IDATE' (NON-UNIQUE)
|