[인덱스 현황]
EC_COURSE_PK = course_code + year + course_sq_no
EC_COURSE_IDX_01 = TEAR
튜닝전
SELECT course_code, year, course_sq_no
FROM ec_course_sq
WHERE course_code = 36
AND year in ('2000','1999')
ORDER by course_code desc, year desc, course_sq_no desc;
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (ORDER BY)
2 1 CONCATENATION
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ'
4 3 INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ'
6 5 INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE)
SORT와 결합으로 늦어 지는속도를
index_desc 를 이용하여 속도 개선
튜닝 후
index_desc 를 이용하여 속도 개선
SELECT /*+index_desc (ec_course_sq EC_COURSE_PK) */ course_code, year, course_sq_no
FROM ec_course_sq
WHERE course_code = 36
AND year in ('2000','1999')
ORDER by course_code desc, year desc, course_sq_no desc;
24 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=2 Card=7 Bytes=63)
1 0 INLIST ITERATOR
2 1 INDEX (RANGE SCAN DESCENDING) OF 'EC_COURSE_SQ_PK' (UNIQ
UE) (Cost=2 Card=7 Bytes=63)
댓글