SQL分页优化五 like+非等值+order by分页

测试验证

如下SQL：

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

有like模糊过滤条件，有非等值过滤条件，有排序。
创建索引：
测试1：

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

Index created.

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

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

Plan hash value: 1798652263

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |      1 |        |     10 |00:00:00.01 |      19 |      5 |
|*  1 |  VIEW                            |             |      1 |     10 |     10 |00:00:00.01 |      19 |      5 |
|*  2 |   COUNT STOPKEY                  |             |      1 |        |     10 |00:00:00.01 |      19 |      5 |
|   3 |    VIEW                          |             |      1 |  35003 |     10 |00:00:00.01 |      19 |      5 |
|   4 |     COUNT                        |             |      1 |        |     10 |00:00:00.01 |      19 |      5 |
|   5 |      VIEW                        |             |      1 |  35003 |     10 |00:00:00.01 |      19 |      5 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| TEST        |      1 |  35003 |     10 |00:00:00.01 |      19 |      5 |
|*  7 |        INDEX FULL SCAN           | IDX_NAME_ID |      1 |  72571 |     23 |00:00:00.01 |       4 |      5 |
------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   6 - filter("OWNER" LIKE 'SYS%')
   7 - access("OBJECT_ID">1000)
       filter("OBJECT_ID">1000)

测试2：

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

Index created.


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


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

Plan hash value: 810954641

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

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

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   7 - access("OBJECT_ID">1000 AND "OWNER" LIKE 'SYS%')
       filter(("OWNER" LIKE 'SYS%' AND "OBJECT_ID">1000))

测试3：

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

Index created.

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


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

Plan hash value: 1258538393

-----------------------------------------------------------------------------------------------------------------------
| 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 |  35003 |     10 |00:00:00.01 |      14 |      2 |
|   4 |     COUNT                        |                   |      1 |        |     10 |00:00:00.01 |      14 |      2 |
|   5 |      VIEW                        |                   |      1 |  35003 |     10 |00:00:00.01 |      14 |      2 |
|   6 |       TABLE ACCESS BY INDEX ROWID| TEST              |      1 |  35003 |     10 |00:00:00.01 |      14 |      2 |
|*  7 |        INDEX FULL SCAN           | IDX_NAME_OWNER_ID |      1 |  35003 |     10 |00:00:00.01 |       4 |      2 |
-----------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   7 - access("OWNER" LIKE 'SYS%' AND "OBJECT_ID">1000)
       filter(("OWNER" LIKE 'SYS%' AND "OBJECT_ID">1000))

从上面三个执行计划可以看到，当过滤条件中有like时，非等值过滤，排序时，应该将排序列放在索引最前面，like和非等值过滤列放后面，二者之间顺序可以互换。