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 SUBSTR(JUSO_CD_ID, 0, 4) = SUBSTR( '1168000000' , 0, 4)
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 SUBSTR(A.JUSO_CD_ID, 0, 4) = SUBSTR( '1168000000' , 0, 4)
AND A.bungyo_hg_cd is null
AND EXISTS (SELECT 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
21 SORT (GROUP BY)
105 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'JGCG01TT' (TABLE)
127 NESTED LOOPS
21 TABLE ACCESS MODE: ANALYZED (FULL) OF 'JGAC08TT' (TABLE)
105 INDEX MODE: ANALYZED (RANGE SCAN) OF 'JGCG01TT_PK'
(INDEX (UNIQUE))
4.2 튜닝 후
Rows Execution Plan
------ --------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
21 SORT (ORDER BY)
21 NESTED LOOPS (SEMI)
21 TABLE ACCESS MODE: ANALYZED (FULL) OF 'JGAC08TT' (TABLE)
21 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'JGCG01TT' (TABLE)
21 INDEX MODE: ANALYZED (RANGE SCAN) OF 'JGCG01TT_PK'
(INDEX (UNIQUE))
|