Oracle优化之单表分页优化(3)

(从上面的执行计划可以看到，sql走 index full scan，只扫描了10条数据（id=7 A-Rows=10）就停止了（id=2 COUNT STOPKEY）一共消耗了5个逻辑读；该执行计划利用索引已经排序的特性只扫描索引获取了10条数据，然后再利用count stopkey特性，获取到分页需要的数据，sql立即停止运行，这才是最佳的执行计划。)

注意：

如果分页语句中有排序（order by）,要利用索引已经排序特性，将order by的列包含在索引中，同时也要利用rownum的count stopkey特性来优化分页sql。如果分页中没有排序，可以直接利用rownum的count stopkey特性来优化分页sql。

例子：

如下两条sq（注意，过滤条件是等值过滤，当然也有order by）l，改成分页语句，并查看执行计划，如下：

select * from t_test where owner='SCOTT' order by object_id;

select * from t_test where owner='SYS' order by object_id;

(其中第一条sql语句的过滤条件是where owner='SCOTT'；该过滤条件能过滤掉表中大部分数据。第二条sql语句的过滤条件where owner='SYS'，能过滤表中一半数据)

---执行第一条sql语句：

SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test where owner='SCOTT' 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  0w9tbrwkn9tn6, child number 0
-------------------------------------
 select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test where owner='SCOTT' 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.04 |    1245 |
|*  1 |  VIEW                            |          |      1 |    10 |    10 |00:00:00.04 |    1245 |
|*  2 |  COUNT STOPKEY                  |          |      1 |        |    10 |00:00:00.04 |    1245 |
|  3 |    VIEW                          |          |      1 |    12 |    10 |00:00:00.04 |    1245 |
|  4 |    COUNT                        |          |      1 |        |    10 |00:00:00.04 |    1245 |
|  5 |      VIEW                        |          |      1 |    12 |    10 |00:00:00.04 |    1245 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| t_test  |      1 |    12 |    10 |00:00:00.04 |    1245 |
|  7 |        INDEX FULL SCAN          | IDX_test |      1 |  80700 |  71901 |00:00:00.01 |    181 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("RN">=1)
  2 - filter(ROWNUM<=10)
  6 - filter("OWNER"='SCOTT')
Note
-----
  - dynamic sampling used for this statement (level=2)
32 rows selected.

---执行第2条语句，如下：