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)) |