改写exists
原SQL
SELECT T.DOC_SYSTEM,
T.DOC_ID,
SUM(T.VIEW_COUNT) VIEW_COUNT,
MAX(T.LAST_VIEW_TIME) LAST_VIEW_TIME
FROM search.DOC_MESSAGE_TRACE T
WHERE EXISTS (SELECT 1
FROM search.DOC_MESSAGE_TRACE ST1
WHERE T.DOC_ID = ST1.DOC_ID
AND ST1.VIEW_COUNT_CHANGE = 'Y'
AND ROWNUM < 100)
GROUP BY T.DOC_ID, T.DOC_SYSTEM;
执行计划
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1867K(100)| | 0 |00:00:02.74 | 2022K|
| 1 | HASH GROUP BY | | 1 | 1 | 38 | 30M| 1867K (1)| 00:01:13 | 0 |00:00:02.74 | 2022K|
|* 2 | FILTER | | 1 | | | | | | 0 |00:00:02.74 | 2022K|
| 3 | TABLE ACCESS FULL| DOC_MESSAGE_TRACE | 1 | 605K| 21M| | 51034 (1)| 00:00:02 | 612K|00:00:00.91 | 194K|
|* 4 | COUNT STOPKEY | | 611K| | | | | | 0 |00:00:01.63 | 1827K|
|* 5 | INDEX RANGE SCAN| DOC_MESSAGE_TRACE_IDX1 | 611K| 1 | 31 | | 3 (0)| 00:00:01 | 0 |00:00:01.37 | 1827K|
------------------------------------------------------------------------------------------------------------------------------------------------
通过等价改写
SELECT T.DOC_SYSTEM,
T.DOC_ID,
SUM(T.VIEW_COUNT) VIEW_COUNT,
MAX(T.LAST_VIEW_TIME) LAST_VIEW_TIME
FROM search.DOC_MESSAGE_TRACE T
WHERE T.DOC_ID in (SELECT ST1.DOC_ID
FROM search.DOC_MESSAGE_TRACE ST1
WHERE ST1.VIEW_COUNT_CHANGE = 'Y'
AND ROWNUM < 100)
GROUP BY T.DOC_ID, T.DOC_SYSTEM
执行计划
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1065 (100)| | 0 |00:00:00.03 | 3953 |
| 1 | HASH GROUP BY | | 1 | 1 | 67 | 1065 (1)| 00:00:01 | 0 |00:00:00.03 | 3953 |
| 2 | NESTED LOOPS | | 1 | 1 | 67 | 1064 (1)| 00:00:01 | 0 |00:00:00.03 | 3953 |
| 3 | NESTED LOOPS | | 1 | 1 | 67 | 1064 (1)| 00:00:01 | 0 |00:00:00.03 | 3953 |
| 4 | VIEW | VW_NSO_1 | 1 | 1 | 29 | 1059 (1)| 00:00:01 | 0 |00:00:00.03 | 3953 |
| 5 | HASH UNIQUE | | 1 | 1 | 31 | | | 0 |00:00:00.03 | 3953 |
|* 6 | COUNT STOPKEY | | 1 | | | | | 0 |00:00:00.03 | 3953 |
|* 7 | INDEX FAST FULL SCAN | DOC_MESSAGE_TRACE_IDX1 | 1 | 1 | 31 | 1059 (1)| 00:00:01 | 0 |00:00:00.03 | 3953 |
|* 8 | INDEX RANGE SCAN | DOC_MESSAGE_TRACE_IDX1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 9 | TABLE ACCESS BY INDEX ROWID| DOC_MESSAGE_TRACE | 0 | 1 | 38 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------
优化效果：执行时间2.74优化到0.03，提升91倍。buffer get从2022K下降到3953 提升523倍
