改写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倍