(从上面可以看出，执行计划中出现了 sort order by，这就意味着分页语句没有利用到索引已经排序的特性，执行计划是错误的，这时候就需要创建正确的索引。)
例子3：
一条sql（，过滤条件有等值条件，也有非等值条件，当然也有order by），如下，将下面的sql分页查询：
select * from t_test where owner='SYS' and object_id > 1000 order by object_name;
--：创建索引（因为owner是等值过滤，object_Id是非等值过滤，创建索引的时候要优先将等值过滤列和排序列组合在一起，然后再将非等值过滤列放到后面）如下：
create index idx_test_4 on t_test(owner,object_name,object_id);
select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_4) */ * from t_test where owner='SYS' and object_id > 1000 order by object_name) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------:
SQL_ID 4z6tjgrdjm5a1, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_4) */ * from t_test where owner='SYS' and
object_id > 1000 order by object_name) a ) where rownum <=10) where rn
>=1
Plan hash value: 1432357471
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 14 | 2 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 14 | 2 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 14 | 2 |
| 3 | VIEW | | 1 | 25683 | 10 |00:00:00.01 | 14 | 2 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 14 | 2 |
| 5 | VIEW | | 1 | 25683 | 10 |00:00:00.01 | 14 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 25683 | 10 |00:00:00.01 | 14 | 2 |
|* 7 | INDEX RANGE SCAN | IDX_test_4 | 1 | 256 | 10 |00:00:00.01 | 4 | 2 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("OWNER"='SYS' AND "OBJECT_ID">1000)
filter("OBJECT_ID">1000)
Note
-----
- dynamic sampling used for this statement (level=2)
34 rows selected.
（可以看出，执行计划中没有出现 sort order by，逻辑读也只有14个，说明执行计划是正确的。）
注意：