ORACLE DB SQL 쿼리 튜닝을 통한 처리속도 저하 요인을 파악하고 개선한 사항 참조하세요[DB 튜닝]

1. SQL 쿼리 속도 저하 요인
DUAL 테이블 5회 Full Scan
Index 컬럼(IDATE) 문자형에서 숫자형으로 변형되어 2회 Full Scan

2. SQL 쿼리 속도 개선 사항
1회 Full Scan (CONNECT BY LEVEL)
Literal 숫자형에서 문자형으로 변경하여 2회 Index Scan

3. SQL 쿼리

3.1 튜닝전 일부 쿼리

SELECT A.KEY, A.CNT, B.KEY, B.CNT <§>
FROM RPT_DAILY
    WHERE IDATE = 20080214 AND
      L1 = 'FW' AND
      L2 = 'SIP' AND
<§>
SELECT KEY, SUM(VALUE) CNT
FROM RPT_DAILY
WHERE IDATE = 20080214 AND
      L1 = 'FW' AND
      L2 = 'SIP' AND
<§>
 ( SELECT 1 NUM FROM DUAL
 UNION
 SELECT 2 NUM FROM DUAL
 UNION
 SELECT 3 NUM FROM DUAL
 UNION
 SELECT 4 NUM FROM DUAL
 UNION
 SELECT 5 NUM FROM DUAL
 ) C

3.2 튜닝후 일부 쿼리

SELECT A.KEY, A.CNT, B.KEY, B.CNT
 <§>
FROM RPT_DAILY
WHERE IDATE = '20080214' AND
          L1 = 'FW' AND
          L2 = 'SIP' AND
 <§>
(SELECT ROWNUM NUM, KEY, CNT FROM
          (SELECT KEY, SUM(VALUE) CNT
           FROM RPT_DAILY
           WHERE IDATE = '20080214' AND
                      L1 = 'FW' AND
                      L2 = 'SIP' AND
 <§>
(SELECT LEVEL NUM
 FROM  DUAL
 CONNECT BY LEVEL <= 5) C

4. 실행 PLAN

4.1 튜닝전

Rows     Execution Plan
-------  -------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      5        UNION-ALL
      1         TABLE ACCESS (FULL) OF 'DUAL'
      1         TABLE ACCESS (FULL) OF 'DUAL'
      1         TABLE ACCESS (FULL) OF 'DUAL'
      1         TABLE ACCESS (FULL) OF 'DUAL'
      1         TABLE ACCESS (FULL) OF 'DUAL'
      5     SORT (JOIN)
     10      VIEW
     10       COUNT (STOPKEY)
     10        VIEW
     10         SORT (ORDER BY STOPKEY)
     20          SORT (GROUP BY)
     20           TABLE ACCESS (FULL) OF 'RPT_DAILY'
     10       VIEW
     10        SORT (ORDER BY STOPKEY)
     16         SORT (GROUP BY)
     20          TABLE ACCESS (FULL) OF 'RPT_DAILY'

4.2 튜닝 후

Rows     Execution Plan
-------  -------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      5       CONNECT BY (WITHOUT FILTERING)
      1        TABLE ACCESS (FULL) OF 'DUAL'
      5     SORT (JOIN)
     10      VIEW
     10       COUNT (STOPKEY)
     10        VIEW
     10         SORT (ORDER BY STOPKEY)
     20          SORT (GROUP BY)
     20           TABLE ACCESS (BY INDEX ROWID) OF 'RPT_DAILY'
  13189            INDEX (RANGE SCAN) OF 'IDX_RPT_DAILY_IDATE'
                       (NON-UNIQUE)
     10       VIEW
     10        SORT (ORDER BY STOPKEY)
     16         SORT (GROUP BY)
     20          TABLE ACCESS (BY INDEX ROWID) OF 'RPT_DAILY'
  13189           INDEX (RANGE SCAN) OF 'IDX_RPT_DAILY_IDATE'
                      (NON-UNIQUE)