今天优化了一个分页的SQL，以前虽然做了上千个SQL的优化，不过都是一些OLAP的，虽然也有OLTP的不过从来没做过分页优化，所以这里记录一下。
SQL和执行计划如下：
SQL> SELECT A.ROWNO,EMS_EVENT_VIEW.* FROM EMS_EVENT_VIEW,
2 (SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY first_occurrence_time DESC) AS ROWNO,EVENT_ID
3 FROM EMS_EVENT_VIEW
WHERE (first_occurrence_time>to_date('2012-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
4 5 and first_occurrence_time or (last_occurrence_time>to_date('2012-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
and last_occurrence_time 6 7 8 WHERE ROWNO>=0 AND ROWNO<=20) A
9 WHERE EMS_EVENT_VIEW.EVENT_ID=A.EVENT_ID;
Plan hash value: 2052413575
-------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:53.37 | 757K| | | |
|* 1 | HASH JOIN | | 1 | 81G| 20 |00:00:53.37 | 757K| 1179K| 1179K| 6598K (0)|
|* 2 | VIEW | | 1 | 2104K| 20 |00:00:30.83 | 101K| | | |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 2104K| 21 |00:00:30.83 | 101K| 4096 | 4096 | 4096 (0)|
|* 4 | FILTER | | 1 | | 1255K|00:00:30.10 | 101K| | | |
| 5 | VIEW | EMS_EVENT_VIEW | 1 | 2104K| 1255K|00:00:28.85 | 101K| | | |
| 6 | UNION-ALL | | 1 | | 1255K|00:00:28.85 | 101K| | | |
| 7 | CONCATENATION | | 1 | | 0 |00:00:00.01 | 335 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| EMS_EVENT | 1 | 1 | 0 |00:00:00.01 | 63 | | | |
|* 9 | INDEX RANGE SCAN | LAST_OCCURRENCE_TIME_INDEX | 1 | 1 | 0 |00:00:00.01 | 63 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| EMS_EVENT | 1 | 1 | 0 |00:00:00.01 | 272 | | | |
|* 11 | INDEX RANGE SCAN | FIRST_OCCURREN |