SELECT
ROWNUM rn, src.cid
FROM (
SELECT /*+ ordered use_nl(csrc, pop) */
pop.cid
FROM
(
SELECT /*+ index(am XPK_AM) */
DISTINCT con.cid, 'aaaa' AS mid
FROM
cat, con, am
WHERE cat.adate = '20111112095102'
AND cat.full LIKE 'CGV%'
AND con.adate = cat.adate
AND con.id = cat.id
AND con.cid NOT IN ( 'MG4', 'MG5', 'M01')
AND con.adate = am.adate
AND con.cid = am.cid
) csrc, pop
WHERE pop.mid = csrc.mid
AND pop.cid = csrc.cid
AND pop.cid <> 'MG49'
ORDER BY pop.point DESC
)src where ROWNUM < 100
빨강색 부분의 내부 쿼리는 0.3초 이내에 끝나는데
외부에 ROWNUM 으로 필터링을 걸어 주니까
몇십초 거의 1분이상 걸립니다.
어떻게 튜닝을 해야할지 문의드립니다.
필터링 걸면 더 빨라질 줄 알았는데 안 그러네요!!!
아래는 PLAN 입니다.
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT ` ` | | 1 | 22 | 216 (2)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 22 | 216 (2)| 00:00:03 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 35 | 216 (2)| 00:00:03 |
| 4 | VIEW | VM_NWVW_1 | 1 | 35 | 215 (2)| 00:00:03 |
| 5 | HASH UNIQUE | | 1 | 219 | 215 (2)| 00:00:03 |
| 6 | NESTED LOOPS | | 3 | 657 | 214 (1)| 00:00:03 |
| 7 | NESTED LOOPS | | 2 | 300 | 7 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 2 | 244 | 5 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | CAT | 1 | 82 | 4 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX2_CAT | 1 | | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDXCON_01 | 4 | 160 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | XPK_AM | 1 | 28 | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | POP | 1 | 69 | 104 (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------
이 PLAN 은 빨강색으로 된 내부 쿼리 PLAN 입니다.
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
|* 1 | SORT ORDER B | | 1 | 35 | 216 (2)| 00:00:03 |
| 2 | VIEW | VM_NWVW_1 | 1 | 35 | 215 (2)| 00:00:03 |
| 3 | HASH UNIQUE | | 1 | 219 | 215 (2)| 00:00:03 |
| 4 | NESTED LOOPS | | 3 | 657 | 214 (1)| 00:00:03 |
| 5 | NESTED LOOPS | | 2 | 300 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 2 | 244 | 5 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | CAT | 1 | 82 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX2_CAT | 1 | | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDXCON_01 | 4 | 160 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | XPK_AM | 1 | 28 | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | POP | 1 | 69 | 104 (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------