oracle更快的分页查询 [英] oracle faster paging query

问题描述

我有两个我想使用的分页查询.

I have two paging query that I consider to use.

第一个是

SELECT * FROM ( SELECT rownum rnum, a.* from (
    select * from members
) a WHERE rownum <= #paging.endRow# ) where rnum > #paging.startRow#

第二个是

SELECT * FROM ( SELECT rownum rnum, a.* from (
    select * from members
) a ) WHERE rnum BETWEEN #paging.startRow# AND #paging.endRow#

您如何看待哪个查询更快?

how do you think which query is the faster one?

推荐答案

看看执行计划，示例中有1000行:

Take a look at the execution plans, example with 1000 rows:

SELECT *
  FROM (SELECT ROWNUM rnum
              ,a.*
          FROM (SELECT *
                  FROM members) a
         WHERE ROWNUM <= endrow#)
 WHERE rnum > startrow#;

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|   2 |   COUNT              |         |       |       |            |          |
|*  3 |    FILTER            |         |       |       |            |          |
|   4 |     TABLE ACCESS FULL| MEMBERS |  1000 | 26000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("RNUM">"STARTROW#")
   3 - filter("MEMBERS"."ENDROW#">=ROWNUM)

和2.

SELECT *
  FROM (SELECT ROWNUM rnum
              ,a.*
          FROM (SELECT *
                  FROM members) a)
 WHERE rnum BETWEEN startrow# AND endrow#;

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|   2 |   COUNT             |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| MEMBERS |  1000 | 26000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("RNUM"<="ENDROW#" AND "RNUM">="STARTROW#")

除此之外，我想说版本2 可以更快一些，因为它减少了一步.但是我不知道您的索引和数据分布，因此您需要自己获取这些执行计划并判断数据的状况.或者只是对其进行测试.

Out of that I'd say version 2 could be slightly faster as it includes one step less. But I don't know about your indexes and data distribution so it's up to you to get these execution plans yourself and judge the situation for your data. Or simply test it.

这篇关于oracle更快的分页查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！