Oracle 分页：分析函数 ROW_NUMBER() VS ROWNUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7073263/
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 Paging: Analytic function ROW_NUMBER() VS ROWNUM
提问by Jimmy
When Paging needs to be done in an web site... Which method performs better?
当需要在网站中进行分页时...哪种方法性能更好？
Analytic function - ROW_NUMBER()
分析函数 - ROW_NUMBER()
- Oracle 10G
http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html
SELECT columnA, columnB FROM (SELECT columnA, columnB, row_number() over (order by columnB) rn FROM table) WHERE rn BETWEEN LOW_LIMIT AND OFFSET;
- 甲骨文 10G
http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html
SELECT columnA, columnB FROM (SELECT columnA, columnB, row_number() over (order by columnB) rn FROM table) WHERE rn BETWEEN LOW_LIMIT AND OFFSET;
ROWNUM
行号
- http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o56asktom-086197.html
INMHO I find this approach a more human-readable code
SELECT * FROM ( SELECT rownum rn, a.* FROM( SELECT columnA, columnB FROM table ORDER BY columnB ) a WHERE rn <= OFFSET ) WHERE rnum >= LOW_LIMITNote: I understand that there are RANK and DENSE_RANK analytic functions, but lets assume I just need page through deterministic queries.
Note 2: To retrieve the total amount of records I am thinking in using a separate simple query count(*)
- http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o56asktom-086197.html
INMHO 我发现这种方法是一种更具人类可读性的代码
SELECT * FROM ( SELECT rownum rn, a.* FROM( SELECT columnA, columnB FROM table ORDER BY columnB ) a WHERE rn <= OFFSET ) WHERE rnum >= LOW_LIMIT注意：我知道有 RANK 和 DENSE_RANK 分析函数，但让我们假设我只需要翻阅确定性查询。
注 2：要使用单独的简单查询 count(*) 来检索我正在考虑的记录总数
回答by Stephen ODonnell
I thought this question was interesting, so I tried a few things out.
我觉得这个问题很有趣，所以我尝试了一些东西。
I have table called large_t, that contains about 1.1M rows.
我有一个叫做 large_t 的表，它包含大约 110 万行。
Then I have two queries:
然后我有两个疑问：
select *
from
(
select rownum rnum, a.*
from (
select owner, object_name, object_id
from large_t
order by object_id
) a
where rownum <= 30
) where rnum > 20;
And
和
select *
from
(
select owner, object_name, object_id,
row_number() over (order by object_id) rnum
from large_t
) where rnum > 20 and rnum <= 30;
If you look at the plans the two queries generate, the first has an operation:
如果您查看两个查询生成的计划，第一个有一个操作：
SORT ORDER BY STOPKEY
While the analytic query contains an operation called
虽然分析查询包含一个名为的操作
WINDOW SORT PUSHED RANK
The SORT ORDER BY STOPKEY is a more efficient sort operation that a plain ORDER BY. I am not sure how a WINDOW SORT PUSHED RANK works, but it seems to work in a similar fashion.
SORT ORDER BY STOPKEY 是一种比普通 ORDER BY 更有效的排序操作。我不确定 WINDOW SORT PUSHED RANK 是如何工作的，但它似乎以类似的方式工作。
Looking at v$sql_workarea after running both queries, both only required a sort_area of 4096 bytes.
运行两个查询后查看 v$sql_workarea，两者都只需要 4096 字节的 sort_area。
In contrast, if I ran the query without a paging query:
相反，如果我在没有分页查询的情况下运行查询：
select owner, object_name, object_id
from large_t
order by object_id
Then the sort area required is 37M, proving the sort in both queries is about the same.
那么需要的排序区域是37M，证明两个查询的排序是差不多的。
Normally, if you want to efficiently return the TOP N of a sorted query, you will want an index on the sorting column - that will prevent Oracle needing to sort at all. So, I created an index on OBJECT_ID, and then explained both queries again.
通常，如果您想有效地返回已排序查询的 TOP N，您将需要在排序列上的索引 - 这将阻止 Oracle 需要进行排序。因此，我在 OBJECT_ID 上创建了一个索引，然后再次解释了这两个查询。
This time the first query used the index and returned in 0.2 seconds, while the second query didn't use the new index and was much slower.
这次第一个查询使用了索引并在0.2秒内返回，而第二个查询没有使用新索引并且速度要慢得多。
So my conclusion from this quick bit of analysis is that in the general case using rownum to filter or the analytic row_number function both perform about the same. However, the rownum example automatically started using the index I created on the table when row_number did not. Maybe I could get it to use the index with some hints - that is something else you can experiment with.
所以我从这个快速分析中得出的结论是，在一般情况下，使用 rownum 进行过滤或分析 row_number 函数的性能大致相同。但是，rownum 示例自动开始使用我在表上创建的索引，而 row_number 没有。也许我可以让它使用带有一些提示的索引 - 这是你可以尝试的其他东西。
回答by Lukas Eder
Apart from the other differences mentioned in answers, you should also consider performance. There is a non-authoritative but very interesting report here, comparing various means of pagination, among which the use of ROWNUMcompared to ROW_NUMBER() OVER():
除了答案中提到的其他差异外，您还应该考虑性能。这里有一篇非权威但很有趣的报道，比较了各种分页方式，其中ROWNUM对比的使用ROW_NUMBER() OVER()：
回答by Ollie
To generate your own empirical results:
要生成您自己的经验结果：
-- Create test table
CREATE TABLE test_large_tab (
tlt_id NUMBER,
tlt_data VARCHAR2(50)
);
-- Load with data
BEGIN
FORALL i IN 1 .. 1000000
INSERT INTO test_large_tab
(
tlt_id,
tlt_data
)
VALUES
(
i,
TO_CHAR(sysdate-i, 'FMMon ddth, YYYY')
);
END;
Of course, you can increase the size of the table to suit your testing purposes!
当然，您可以增加表的大小以满足您的测试目的！
Set timing on and run both queries against the large table.
设置计时并对大表运行两个查询。
Change the table structure to better suit your test as you may want some columns to be indexed for your query too etc. but essentially it is a simple test and won't take you long to run.
更改表结构以更好地适合您的测试，因为您可能希望某些列也为您的查询建立索引等。但本质上它是一个简单的测试，不会花很长时间运行。
If both come out roughly the same timings then use the most readable (and hence supportable) version.
如果两者的时间大致相同，则使用最易读（因此可支持）的版本。
