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

3. SQL 쿼리

3.1 튜닝전 일부 쿼리
SELECT A.HG_CD, HG_NM, GWANHAL_JIYOOK_CD, GWANHAL_SIDO_CD,
        HIGH_JONGRYU_GB, GUKRIP_GB, JUSO_CD_ID, ZIP_CODE, JUSO1,
       JUSO2, TEL_NO, FAX_NO, HOMEPAGE_JUSO, UPDATE_DT,
       UPDATE_MAN, HG_JONGRYU_GB
FROM JGDBMGR.JGAC08TT A , (select hg_cd, jg_year from JGDBMGR.jgcg01tt where jg_year = '2009' and use_yn = 'Y' group by hg_cd, jg_year ) b
WHERE  HG_JONGRYU_GB = '04'
   AND HG_NM LIKE '%원주%'
   and a.hg_cd = b.hg_cd
   and a.bungyo_hg_cd is null
ORDER BY  HG_NM

3.2 튜닝후 일부 쿼리
SELECT A.HG_CD, A.HG_NM, A.GWANHAL_JIYOOK_CD,
          A.GWANHAL_SIDO_CD, A.HIGH_JONGRYU_GB, A.GUKRIP_GB,
          A.JUSO_CD_ID, A.ZIP_CODE, A.JUSO1, A.JUSO2,
          A.TEL_NO, A.FAX_NO, A.HOMEPAGE_JUSO, A.UPDATE_DT, 
          A.UPDATE_MAN, A.HG_JONGRYU_GB
FROM  JGDBMGR.JGAC08TT A
WHERE A.HG_JONGRYU_GB = '04'
   AND A.HG_NM LIKE '%원주%'
   AND A.bungyo_hg_cd is null
   AND EXISTS (SELECT /*+ INDEX(B JGCG01TT_PK) */ 1
                    FROM   JGDBMGR.jgcg01tt B
                    WHERE  B.jg_year = '2009'
                        AND  A.hg_cd = B.hg_cd
                        AND  B.use_yn = 'Y')
ORDER BY  A.HG_NM

4. 실행 PLAN

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

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