CE_INDEX | 1 | 1 | 0 |00:00:00.01 | 272 | | | |
|* 12 | VIEW | index_join_006 | 1 | 2104K| 1255K|00:00:28.84 | 100K| | | |
|* 13 | HASH JOIN | | 1 | | 3863K|00:00:26.50 | 100K| 195M| 9M| 248M (0)|
|* 14 | HASH JOIN | | 1 | | 3863K|00:00:13.87 | 63020 | 160M| 10M| 214M (0)|
| 15 | PARTITION RANGE ALL | | 1 | 2104K| 3863K|00:00:00.01 | 31419 | | | |
| 16 | INDEX FAST FULL SCAN | IDX_FIRSTTIME_201202 | 338 | 2104K| 3863K|00:00:00.07 | 31419 | | | |
| 17 | PARTITION RANGE ALL | | 1 | 2104K| 3863K|00:00:00.01 | 31601 | | | |
| 18 | INDEX FAST FULL SCAN | IDX_LASTOCCURRENCE_201202 | 338 | 2104K| 3863K|00:00:00.06 | 31601 | | | |
| 19 | INDEX FAST FULL SCAN | PK_EMS_EVENT_HISTORY_201202 | 1 | 2104K| 3863K|00:00:00.01 | 37894 | | | |
| 20 | VIEW | EMS_EVENT_VIEW | 1 | 3864K| 3867K|00:00:19.34 | 656K| | | |
| 21 | UNION-ALL | | 1 | | 3867K|00:00:15.47 | 656K| | | |
| 22 | TABLE ACCESS FULL | EMS_EVENT | 1 | 3867 | 3950 |00:00:00.02 | 2046 | | | |
| 23 | PARTITION RANGE ALL | | 1 | 3860K| 3863K|00:00:07.73 | 654K| | | |
| 24 | TABLE ACCESS FULL | EMS_EVENT_HISTORY | 338 | 3860K| 3863K|00:00:09.51 | 654K| | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMS_EVENT_VIEW"."EVENT_ID"="from_subquery_003"."EVENT_ID")
2 - filter(("ROWNO">=:SYS_B_8 AND "ROWNO"<=:SYS_B_9))
3 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("FIRST_OCCURRENCE_TIME") DESC )<=:SYS_B_9)
4 - filter(:SYS_B_8<=:SYS_B_9)
9 - access("A"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5) AND "A"."LAST_OCCURRENCE_TIME" 10 - filter((LNNVL("A"."LAST_OCCURRENCE_TIME"TO_DATE(:SYS_B_4,:SYS_B_5))))
11 - access("A"."FIRST_OCCURRENCE_TIME">TO_DATE(:SYS_B_0,:SYS_B_1) AND "A"."FIRST_OCCURRENCE_TIME" 12 - filter((("B"."FIRST_OCCURRENCE_TIME">TO_DATE(:SYS_B_0,:SYS_B_1) AND "B"."FIRST_OCCURRENCE_TIME" ("B"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5) AND "B"."LAST_OCCURRENCE_TIME" 13 - access(ROWIDROWID=ROWID)
14 - access(ROWIDROWID=ROWID)
如果你看不清楚SQL，我在这里再贴一下：
SELECT A.ROWNO, EMS_EVENT_VIEW.*
FROM EMS_EVENT_VIEW,
(SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY first_occurrence_time DESC) AS ROWN |