URPOSE
마지막 건을 찾기 위해 SUB-QUERY에 MAX를 취해 마지막건을 추출함으로써 동일한 테이블의
데이터를 두 번 ACCESS 함으로 비효율이 발생됨.
이것을 INDEX_DESC의 HINT를 사용하여 한번만 데이터를 ACCESS하도록 개선한 사례이다.
- 대용량 데이터베이스 솔류션I의 부분 범위 처리 활용 방법 참조
- 해당 내용은 실무의 모든 시스템에 비슷한 사례가 반드시 존재하고 있음.
TUNING 전
- SQL
select MEM.rowid ,MEM.MEMO_BAN ,MEM.MEMO_ID,
TO_CHAR(MEM.SYS_CREATION_DATE, 'YYYYMMDDHH24MISS')
TO_CHAR(MEM.MEMO_DATE,'YYYYMMDDHH24MISS') ,
MEM.MEMO_TYPE ,MEM.MEMO_SUBSCRIBER ,MEM.MEMO_SYSTEM_TXT
from USERS U ,MEMO_TYPE MTP ,MEMO MEM
where (((MEM.MEMO_BAN=:b36 and MEM.MEMO_ID= (select max(MEM1.MEMO_ID)
from MEMO MEM1
where MEM1.MEMO_BAN=:b36))
and MEM.MEMO_TYPE=MTP.MEMTP_MEMO_TYPE) and MEM.OPERATOR_ID=U.USER_ID(+)) - Tracle 결과
call count cpu elapsed disk query current rows
------ ------ ------ -------- ------ -------- ------- --------
Parse 5941 4.98 5.42 0 0 0 0
Execute 6923 8.60 45.92 5191 28797 0 2701
Fetch 6923 7.03 35.98 2799 67232 0 6923
------- ------ ------ ------- ------- -------- ------- --------
total 19787 20.61 87.32 7990 96029 0 9624
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
1 NESTED LOOPS (OUTER)
1 NESTED LOOPS
1 TABLE ACCESS (BY ROWID) OF 'MEMO'
1 INDEX (UNIQUE SCAN) OF 'MEMO_PK' (UNIQUE)
18 SORT (AGGREGATE)
19 INDEX (RANGE SCAN) OF 'MEMO_PK' (UNIQUE)
1 TABLE ACCESS (BY ROWID) OF 'MEMO_TYPE'
1 INDEX (UNIQUE SCAN) OF 'MEMO_TYPE_PK' (UNIQUE)
1 TABLE ACCESS (BY ROWID) OF 'USERS'
2 INDEX (RANGE SCAN) OF 'USERS_PK' (UNIQUE) ******** *******************************************************
TUNING 후
- SQL
select /*+ ORDERED USE_NL(MEM U MTP) INDEX_DESC(MEM MEMO_PK) +*/
MEM.rowid ,MEM.MEMO_BAN ,MEM.MEMO_ID,
TO_CHAR(MEM.SYS_CREATION_DATE, 'YYYYMMDDHH24MISS'),
TO_CHAR(MEM.MEMO_DATE,'YYYYMMDDHH24MISS') , MEM.MEMO_TYPE,
MEM.MEMO_SUBSCRIBER ,MEM.MEMO_SYSTEM_TXT
from MEMO MEM ,USERS U ,MEMO_TYPE MTP
where MEM.MEMO_BAN=:b36
and MEM.MEMO_TYPE=MTP.MEMTP_MEMO_TYPE)
and MEM.OPERATOR_ID=U.USER_ID(+)
and rownum =1 - Trace
call count cpu elapsed disk query current rows
------ ------ ------ -------- ------ -------- ------- --------
Parse 1144 0.95 1.07 0 0 0 0
Execute 1353 0.41 0.36 0 0 0 596
Fetch 1353 2.14 7.75 1237 13202 0 1353
------- ----- ------ -------- ------ -------- ------- --------
total 3850 3.5 9.18 1237 13202 0 1949
Rows Execution Plan
------- -------------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
1 COUNT (STOPKEY)
1 NESTED LOOPS
1 NESTED LOOPS (OUTER)
1 TABLE ACCESS (BY ROWID) OF 'MEMO'
1 INDEX (RANGE SCAN DESCENDING) OF 'MEMO_PK' (UNIQUE)
1 TABLE ACCESS (BY ROWID) OF 'USERS'
1 INDEX (RANGE SCAN) OF 'USERS_PK' (UNIQUE)
1 TABLE ACCESS (BY ROWID) OF 'MEMO_TYPE'
1 INDEX (UNIQUE SCAN) OF 'MEMO_TYPE_PK' (UNIQUE)
******* *******************************************************
개선 사항
- 하루에 10번 사용되고 응답속도가 10초 소요되는 SQL문을 0.5초로 개선하는 것 보다
하루에 2000번 사용되고 응답속도가 0.1초 소요되는 sql문을 0.01초로 개선하는 것이
더 중요하다.