(因为sql语句没有where过滤条件，强制走索引只能走index full scan，无法走索引范围扫描（index range scan）。E-Rows的显示 说明了扫描了索引中所有叶子块，一共消耗了1245个逻辑读；理想的执行计划是：index full scan只扫描一个最多几个索引叶子块，最根本的原因还在于这个分页框架错了！)
2、正确的分页框架：
语法：select * from (select * from (select a.*,rownum rn from (需要分页的sql) a ) where rownum <=10) where rn >=1;
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test order by object_id) a ) where rownum <=10) where rn >=1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID cgjp65zfj1yqa, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test order by object_id) a ) where
rownum <=10) where rn >=1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 | 80700 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 | 80700 | 10 |00:00:00.01 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 80700 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_test | 1 | 80700 | 10 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
31 rows selected.