SQL分页优化四 等值+非等值+order by分页

测试验证

现有如下SQL，每页显示10条： 
select * 
 from test 
where owner = 'SYS' 
  and object_id > 1000 
order by object_name; 

select * 
 from (select * 
         from (select a.*, rownum rn 
                 from (select * 
                         from test 
                        where owner = 'SYS' 
                          and object_id > 1000 
                        order by object_name) a) 
        where rownum <= 10) 
where rn >= 1;

分页语句中有排序列，创建索引的时候，需要将排序列包含在索引中。所以只需要将过滤列owner、object_id以及排序列object_name组合起来创建索引。
因为owner是等值过滤，object_id列是非等值过滤，创建索引时，优先将等值过滤和排序列组合起来，然后再将非等值过滤列放在后面。

SQL> create index idx_owner_name_id on test(owner,object_name,object_id);

Index created.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6n5yu1h14fdrh, child number 0
-------------------------------------
select *   from (select *           from (select a.*, rownum rn
          from (select /*+index(test idx_owner_name_id)*/
           *                           from test
    where owner = 'SYS'                            and object_id > 1000
                         order by object_name) a)          where rownum
<= 10)  where rn >= 1

Plan hash value: 2090516350

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |      1 |        |     10 |00:00:00.01 |      15 |
|*  1 |  VIEW                            |                   |      1 |     10 |     10 |00:00:00.01 |      15 |
|*  2 |   COUNT STOPKEY                  |                   |      1 |        |     10 |00:00:00.01 |      15 |
|   3 |    VIEW                          |                   |      1 |  31163 |     10 |00:00:00.01 |      15 |
|   4 |     COUNT                        |                   |      1 |        |     10 |00:00:00.01 |      15 |
|   5 |      VIEW                        |                   |      1 |  31163 |     10 |00:00:00.01 |      15 |
|   6 |       TABLE ACCESS BY INDEX ROWID| TEST              |      1 |  31163 |     10 |00:00:00.01 |      15 |
|*  7 |        INDEX RANGE SCAN          | IDX_OWNER_NAME_ID |      1 |  31163 |     10 |00:00:00.01 |       5 |
--------------------------------------------------------------------------------------------------------------

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)

从执行计划中看到，没有sort order by。逻辑读也才15个，说明执行计划非常理想。

尝试创建以下索引：

SQL> create index idx_owner_id_name on test(owner,object_id,object_name);

Index created.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4ypy16hfrrfv5, child number 0
-------------------------------------
select *   from (select *           from (select a.*, rownum rn
          from (select /*+index(test idx_owner_id_name)*/
           *                           from test
    where owner = 'SYS'                            and object_id > 1000
                         order by object_name) a)          where rownum
<= 10)  where rn >= 1

Plan hash value: 2498002320

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   |      1 |        |     10 |00:00:00.04 |    1003 |    191 |       |       |          |
|*  1 |  VIEW                             |                   |      1 |     10 |     10 |00:00:00.04 |    1003 |    191 |       |       |          |
|*  2 |   COUNT STOPKEY                   |                   |      1 |        |     10 |00:00:00.04 |    1003 |    191 |       |       |          |
|   3 |    VIEW                           |                   |      1 |  31163 |     10 |00:00:00.04 |    1003 |    191 |       |       |          |
|   4 |     COUNT                         |                   |      1 |        |     10 |00:00:00.04 |    1003 |    191 |       |       |          |
|   5 |      VIEW                         |                   |      1 |  31163 |     10 |00:00:00.04 |    1003 |    191 |       |       |          |
|   6 |       SORT ORDER BY               |                   |      1 |  31163 |     10 |00:00:00.04 |    1003 |    191 |  4376K|   884K| 3889K (0)|
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST              |      1 |  31163 |  29945 |00:00:00.03 |    1003 |    191 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | IDX_OWNER_ID_NAME |      1 |  31163 |  29945 |00:00:00.02 |     189 |    191 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   8 - access("OWNER"='SYS' AND "OBJECT_ID">1000 AND "OBJECT_ID" IS NOT NULL)

执行计划中有sort order by，说明没有用到索引已经排序特性，而且逻辑读为1003个，说明执行计划是错误的。
这是因为该分页语句是根据object_name进行排序的，在创建索引的时候是按照owner，object_id，object_name顺序创建索引的。
按照索引排序的方式从表中取出5条数据观察：

SQL> select *
  2    from (select rownum rn, owner, object_id, object_name
  3            from test
  4           where owner = 'SYS'
  5             and object_id > 1000
  6           order by owner, object_id, object_name)
  7   where rownum <= 5;

        RN OWNER  OBJECT_ID OBJECT_NAME
---------- ----- ---------- ---------------
         1 SYS         1001 NOEXP$
         2 SYS         1002 EXPPKGOBJ$
         3 SYS         1003 I_OBJTYPE
         4 SYS         1004 EXPPKGACT$
         5 SYS         1005 I_ACTPACKAGE

在这前5条数据中，我们按照分页语句排序条件object_name进行排序，应该是第4行数据显示为第一行数据，但是它在索引中排到了第4行，所以索引中数据的顺序并不能满足分页语句中的排序要求，这就产生了sort order by，进而导致执行计划出错。

按照正确的owner，object_name，object_id顺序创建索引时，索引中前5条数据：

SQL> select *   
  2    from (select rownum rn, owner, object_id, object_name
  3            from test
  4           where owner = 'SYS'
  5             and object_id > 1000
  6           order by owner, object_name, object_id)
  7   where rownum <= 5;

        RN OWNER  OBJECT_ID OBJECT_NAME
---------- ----- ---------- ----------------------------------------
         1 SYS        34042 /1000323d_DelegateInvocationHa
         2 SYS        44844 /1000e8d1_LinkedHashMapValueIt
         3 SYS        23397 /1005bd30_LnkdConstant
         4 SYS        19737 /10076b23_OraCustomDatumClosur
         5 SYS        45460 /100c1606_StandardMidiFileRead

索引中的数据顺序完全符合分页语句中的排序要求，这就不需要进行sort order by，所以执行计划是正确的。