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

3. SQL 쿼리

3.1 튜닝전 일부 쿼리
SELECT A.JG_YEAR,
          A.JG_CHASU,
          B.GIJUN_DT
FROM JGDBMGR.JGCG01TT A,
        JGDBMGR.JGCG02TT B
WHERE A.JG_YEAR  = B.JG_YEAR
     AND A.JG_CHASU = B.JG_CHASU
     AND A.USE_YN   = 'Y'
GROUP BY A.JG_YEAR,
         A.JG_CHASU,
         B.GIJUN_DT
order by jg_year desc, jg_chasu desc, gijun_dt desc

3.2 튜닝후 일부 쿼리
SELECT B.JG_YEAR, B.JG_CHASU, B.GIJUN_DT
FROM   JGDBMGR.JGCG02TT B
WHERE EXISTS (SELECT /*+ INDEX(A JGCG01TT_PK) */ 1
              FROM JGDBMGR.JGCG01TT A
              WHERE A.JG_YEAR  = B.JG_YEAR
                AND A.JG_CHASU = B.JG_CHASU
                AND A.USE_YN   = 'Y')
order by jg_year desc, jg_chasu desc, gijun_dt desc

4. 실행 PLAN

4.1 튜닝전
Rows     Execution Plan
------  --------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      4   SORT (GROUP BY)
39377    HASH JOIN
      5     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'JGCG02TT' (TABLE)
62107     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'JGCG01TT' (TABLE)

4.2 튜닝 후
Rows     Execution Plan
------  --------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      4   SORT (ORDER BY)
      4    NESTED LOOPS (SEMI)
      5     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'JGCG02TT' (TABLE)
      4     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)  
               OF 'JGCG01TT‘ (TABLE)
      4      INDEX   MODE: ANALYZED (FULL SCAN) OF 'JGCG01TT_PK' (INDEX
                 (UNIQUE))