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)