SQL分页优化三 降序排序+分页

测试验证

如下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，这就说明分页语句没有利用到索引已经排好序的特性，执行计划是错误的，这时需要创建正确的索引。