单表分页优化思路：
--创建测试表：
SQL> create table t_test as select * from dba_objects;
Table created.
如，下面的sql （没有过滤条件，只有排序），要将查询结果分页显示，每页显示10条，如：
select * from t_test order by object_id;
例子：
1、分页查询sql语句，如下（通常会采用下面的方法，但是这是错误的分页框架）
语法：select * from (select t.*,rownum rn from (需要分页的sql)t ) where rn >=1 and rn <=10;
SQL> select * from (select t.*,rownum rn from (select * from t_test order by object_id) t) where rn >=1 and rn <=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3603170480
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80700 | 16M| | 3918 (1)| 00:00:48 |
|* 1 | VIEW | | 80700 | 16M| | 3918 (1)| 00:00:48 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 80700 | 15M| | 3918 (1)| 00:00:48 |
| 4 | SORT ORDER BY | | 80700 | 15M| 19M| 3918 (1)| 00:00:48 |
| 5 | TABLE ACCESS FULL| t_test | 80700 | 15M| | 287 (1)| 00:00:04 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=10 AND "RN">=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
331 recursive calls
0 db block gets
1211 consistent gets
1024 physical reads
0 redo size
2075 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
从上面的执行计划可以看出，该sql走了全表扫描，如果该表很大，则先要将该表排序，再取出其中10行数据，这样会出现严重的性能问题，所以该sql不能走全表扫描，必须走索引扫描。（因为索引已经排序了，可使用索引来消除排序）一般分页语句中都有排序。
如下，在object_id列建立索引，并在索引中添加一个常量0，如下：（在索引中添加一个常量0，是因为object_id列允许位null，如果不添加常量（不一定是0，可以是1,2,3，也可以是英文字母），索引中就不能存储null值，；因为sql中并没有剔除null值，所以我们必须要添加一个常量，让索引存储null，这样才能使sql走索引。）
SQL> create index idx_test on t_test(object_id,0);
SQL> select * from (select t.*,rownum rn from (select /*+ index)t_test idx_test) */ * from t_test order by object_id) t) where rn >=1 and rn <=10;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID gvq2rvmutr85w, child number 0
-------------------------------------
select * from (select t.*,rownum rn from (select /*+ index)t_test
idx_test) */ * from t_test order by object_id) t) where rn >=1 and rn
<=10
Plan hash value: 3119682446
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.08 | 1245 |
|* 1 | VIEW | | 1 | 80700 | 10 |00:00:00.08 | 1245 |
| 2 | COUNT | | 1 | | 71903 |00:00:00.07 | 1245 |
| 3 | VIEW | | 1 | 80700 | 71903 |00:00:00.06 | 1245 |
| 4 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 80700 | 71903 |00:00:00.04 | 1245 |
| 5 | INDEX FULL SCAN | IDX_test | 1 | 80700 | 71903 |00:00:00.01 | 181 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN"<=10 AND "RN">=1))
Note
-----
- dynamic sampling used for this statement (level=2)
28 rows selected.