测试验证
如下SQL：
select *
from (select *
from (select a.*, rownum rn
from (select *
from test
order by object_id,object_name desc) a)
where rownum <= 10)
where rn >= 1;创建索引：
SQL> create index idx_test_id_name on test(object_id,object_name desc,0);
Index created.SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID 4ksmz0g2zjwtj, child number 0
-------------------------------------
select * from (select * from (select a.*, rownum rn
from (select /*+index(test idx_test_id_name)*/
* from test
order by object_id, object_name desc) a) where rownum <=
10) where rn >= 1
Plan hash value: 445348578
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | 4 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 | 4 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 | 4 |
| 3 | VIEW | | 1 | 74510 | 10 |00:00:00.01 | 5 | 4 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 | 4 |
| 5 | VIEW | | 1 | 74510 | 10 |00:00:00.01 | 5 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 74510 | 10 |00:00:00.01 | 5 | 4 |
| 7 | INDEX FULL SCAN | IDX_TEST_ID_NAME | 1 | 74510 | 10 |00:00:00.01 | 3 | 4 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)如果创建索引时，将object_name在前，object_id列在后，这个时候，索引中列先后顺序与分页语句中排序列先后顺序不一致，强制走索引，就会出现sort order by。因为索引的顺序与排序的顺序不一致，所以需要从索引获取数据之后在排序，有排序就会出现sort order by。
测试：
SQL> create index idx_test_name_id on test(object_name,object_id,0);
Index created.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a80tzbw35yk28, child number 0
-------------------------------------
select * from (select * from (select a.*, rownum rn
from (select /*+index(test idx_test_name_id)*/
* from test
order by object_id, object_name desc) a) where rownum <=
10) where rn >= 1
Plan hash value: 2869317785
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.11 | 37338 | 431 | | | |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.11 | 37338 | 431 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.11 | 37338 | 431 | | | |
| 3 | VIEW | | 1 | 74510 | 10 |00:00:00.11 | 37338 | 431 | | | |
| 4 | COUNT | | 1 | | 10 |00:00:00.11 | 37338 | 431 | | | |
| 5 | VIEW | | 1 | 74510 | 10 |00:00:00.11 | 37338 | 431 | | | |
| 6 | SORT ORDER BY | | 1 | 74510 | 10 |00:00:00.11 | 37338 | 431 | 10M| 1282K| 9M (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 74510 | 72571 |00:00:00.08 | 37338 | 431 | | | |
| 8 | INDEX FULL SCAN | IDX_TEST_NAME_ID | 1 | 74510 | 72571 |00:00:00.05 | 431 | 431 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)从执行计划看到，出现了sort order by，这就说明分页语句没有利用到索引已经排好序的特性，执行计划是错误的，这时需要创建正确的索引。
