oracle 更快的分页查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11680364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle faster paging query
提问by Patrick Jeon
I have two paging query that I consider to use.
我有两个我考虑使用的分页查询。
First one is
第一个是
SELECT * FROM ( SELECT rownum rnum, a.* from (
select * from members
) a WHERE rownum <= #paging.endRow# ) where rnum > #paging.startRow#
And the Second is
第二个是
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?
你认为哪个查询更快？
采纳答案by GWu
Take a look at the execution plans, example with 1000 rows:
看一下执行计划，例如有 1000 行：
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)
And 2.
和 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#")
Out of that I'd say version 2 couldbe 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.
除此之外，我想说第 2 版可能会稍微快一点，因为它少了一步。但是我不知道您的索引和数据分布情况，因此由您自己获取这些执行计划并判断数据的情况。或者干脆测试一下。
回答by Alessandro Rossi
I don't actually have availability of Oracle now but the best SQL query for paging is the following for sure
我现在实际上没有 Oracle 的可用性，但最好的分页 SQL 查询肯定如下
select *
from (
select rownum as rn, a.*
from (
select *
from my_table
order by ....a_unique_criteria...
) a
)
where rownum <= :size
and rn > (:page-1)*:size
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
To achieve a consistent pagingyou should order rows using a unique criteria, doing so will avoid to load for page X a row you already loaded for a page Y ( !=X ).
为了实现一致的分页，您应该使用唯一的标准对行进行排序，这样做将避免为页面 X 加载您已经为页面 Y 加载的行 (!=X)。
EDIT:
编辑：
1) Order rows using a unique criteria means to order data in way that each row will keep the same position at every execution of the query
1) 使用唯一条件对行进行排序意味着以这样的方式对数据进行排序，即每行在每次执行查询时都保持相同的位置
2) An index with all the expressions used on the ORDER BY clause will help getting results faster, expecially for the first pages. With that index the execution plan choosen by the optimizer doesn't needs to sort the rows because it will return rows scrolling the index by its natural order.
2) 包含 ORDER BY 子句中使用的所有表达式的索引将有助于更快地获得结果，尤其是第一页。使用该索引，优化器选择的执行计划不需要对行进行排序，因为它将返回按其自然顺序滚动索引的行。
3) By the way, the fastests way to page result from a query is to execute the query only once and to handle all the flow from the application side.
3) 顺便说一句，从查询结果分页的最快方法是只执行一次查询并处理来自应用程序端的所有流程。
回答by Vadim Kirilchuk
A already answered in hereBut let me copypaste.
A 已经在这里回答但让我复制粘贴。
Just want to summarize the answers and comments. There are a number of ways doing a pagination.
只是想总结一下答案和评论。有多种方法可以进行分页。
Prior to oracle 12c there were no OFFSET/FETCH functionality, so take a look at whitepaper as the @jasonk suggested. It's the most complete article I found about different methods with detailed explanation of advantages and disadvantages. It would take a significant amount of time to copy-paste them here, so I want do it.
在 oracle 12c 之前，没有 OFFSET/FETCH 功能，因此请按照 @jasonk 的建议查看白皮书。这是我找到的关于不同方法的最完整的文章，其中详细说明了优缺点。将它们复制粘贴到这里需要花费大量时间，所以我想这样做。
There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination. jooq's blogpost
jooq 创建者也有一篇很好的文章，解释了 oracle 和其他数据库分页的一些常见注意事项。jooq 的博文
Good news, since oracle 12c we have a new OFFSET/FETCH functionality. OracleMagazine 12c new features. Please refer to "Top-N Queries and Pagination"
好消息，从 oracle 12c 开始，我们有了新的 OFFSET/FETCH 功能。OracleMagazine 12c 新特性。请参考“Top-N查询和分页”
You may check your oracle version by issuing the following statement
您可以通过发出以下语句来检查您的 oracle 版本
SELECT * FROM V$VERSION
