ORACLE分页查询基于索引的考虑

分页查询在应用中使用非常广泛，在oracle中最常用的做法是使用rownum进行分页查询。  比如：

select *
  from (select rownum rnm, z.*
          from (select *
                  from t 
                  where Sell_member_id = 'wangsai'
                 order by YYYYMMDD desc, score desc,buy_member_id desc
                 ) z
          where rownum<=6
       )
 where rnm > 0

上面SQL中我们只对Sell_member_id做筛选条件，而对YYYYMMDD desc, score desc,buy_member_id desc个字段进行了排序操作。首先我们看第一种情况，只在Sell_member_id 上建立一个索引。并进行它查看执行计划。

---------------------------------------------------------------------------
| Id  | Operation                               | Name                    |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                         |
|*  1 |  VIEW                                   |                         |
|*  2 |   COUNT STOPKEY                         |                         |
|   3 |    VIEW                                 |                         |
|*  4 |     SORT ORDER BY STOPKEY               |                         |
|*  5 |      FILTER                             |                         |
|*  6 |       TABLE ACCESS BY GLOBAL INDEX ROWID| CN_BRMMS_M_SCORE_SUMDT1 |
|*  7 |        INDEX RANGE SCAN                 | IDX_CN_BRMMS_M_SCORE_S  |
---------------------------------------------------------------------------

接下来我们基于所有的排序字段 YYYYMMDD，score ，buy_member_id字段建立索引，重新查看执行计划，我们可以看到执行计划中少了一次SORT ORDER BY STOPKEY,同时也会减少大量的一致性读(consistent gets)操作。

----------------------------------------------------------------------------
| Id  | Operation                              | Name                      |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                           |
|*  1 |  VIEW                                  |                           |
|*  2 |   COUNT STOPKEY                        |                           |
|   3 |    VIEW                                |                           |
|*  4 |     FILTER                             |                           |
|   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| CN_BRMMS_M_SCORE_SUMDT1   |
|*  6 |       INDEX RANGE SCAN DESCENDING      | IDX_CN_BRMMS_M_SCORE_SYSB |
----------------------------------------------------------------------------

 

然而排序字段的排序顺序对执行计划有影响吗,答案是肯定的，当我们改成：order by YYYYMMDD desc, score desc,buy_member_id asc时再次查看执行计划，同样会有SORT ORDER BY STOPKEY。

------------------------------------------------------------------------------
| Id  | Operation                               | Name                      |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |
|*  1 |  VIEW                                   |                           |
|*  2 |   COUNT STOPKEY                         |                           |
|   3 |    VIEW                                 |                           |
|*  4 |     SORT ORDER BY STOPKEY               |                           |
|*  5 |      FILTER                             |                           |
|   6 |       TABLE ACCESS BY GLOBAL INDEX ROWID| CN_BRMMS_M_SCORE_SUMDT1   |
|*  7 |        INDEX RANGE SCAN                 | IDX_CN_BRMMS_M_SCORE_SYSB |
------------------------------------------------------------------------------

总结：
  
oracle分页查询一般是WEB访问应用，对查询速度要求极高。当分页中又涉及排序的操作时，正确的B树索引建立，有效的避免排序. 因为B树索引是按顺序组织数据在树的叶子节点上。
同时我们要注意的是保持排序的一致性。
另外：在使用rownum排序分页时一定要保证排序字段的唯一性，不然会导致分页错误。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/77270/viewspace-604862/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/77270/viewspace-604862/