如何在 Oracle 中加速 row_number？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/827108/
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
How can I speed up row_number in Oracle?
提问by Jason Baker
I have a SQL query that looks something like this:
我有一个看起来像这样的 SQL 查询：
SELECT * FROM(
SELECT
...,
row_number() OVER(ORDER BY ID) rn
FROM
...
) WHERE rn between :start and :end
Essentially, it's the ORDER BY part that's slowing things down. If I were to remove it, the EXPLAIN cost goes down by an order of magnitude (over 1000x). I've tried this:
本质上，是 ORDER BY 部分减慢了速度。如果我要删除它，解释成本会下降一个数量级（超过 1000 倍）。我试过这个：
SELECT
...
FROM
...
WHERE
rownum between :start and :end
But this doesn't give correct results. Is there any easy way to speed this up? Or will I have to spend some more time with the EXPLAIN tool?
但这并没有给出正确的结果。有什么简单的方法可以加快速度吗？还是我需要花更多时间使用 EXPLAIN 工具？
回答by Quassnoi
ROW_NUMBERis quite inefficient in Oracle.
ROW_NUMBER在 中效率很低Oracle。
See the article in my blog for performance details:
性能详情请看我博客中的文章：
For your specific query, I'd recommend you to replace it with ROWNUMand make sure that the index is used:
对于您的特定查询，我建议您将其替换为ROWNUM并确保使用索引：
SELECT *
FROM (
SELECT /*+ INDEX_ASC(t index_on_column) NOPARALLEL_INDEX(t index_on_column) */
t.*, ROWNUM AS rn
FROM table t
ORDER BY
column
)
WHERE rn >= :start
AND rownum <= :end - :start + 1
This query will use COUNT STOPKEY
此查询将使用 COUNT STOPKEY
Also either make sure you columnis not nullable, or add WHERE column IS NOT NULLcondition.
也要么确保您column不可为空，要么添加WHERE column IS NOT NULL条件。
Otherwise the index cannot be used to retrieve all values.
否则索引不能用于检索所有值。
Note that you cannot use ROWNUM BETWEEN :start and :endwithout a subquery.
请注意，您不能在没有ROWNUM BETWEEN :start and :end子查询的情况下使用。
ROWNUMis always assigned last and checked last, that's way ROWNUM's always come in order without gaps.
ROWNUM总是最后分配并最后检查，这就是方式ROWNUM总是按顺序进行而没有间隙。
If you use ROWNUM BETWEEN 10 and 20, the first row that satisifies all other conditions will become a candidate for returning, temporarily assigned with ROWNUM = 1and fail the test of ROWNUM BETWEEN 10 AND 20.
如果使用ROWNUM BETWEEN 10 and 20，则满足所有其他条件的第一行将成为返回的候选者，暂时分配ROWNUM = 1并未通过 的测试ROWNUM BETWEEN 10 AND 20。
Then the next row will be a candidate, assigned with ROWNUM = 1and fail, etc., so, finally, no rows will be returned at all.
然后下一行将是一个候选，分配ROWNUM = 1和失败等，所以，最后，根本不会返回任何行。
This should be worked around by putting ROWNUM's into the subquery.
这应该通过将ROWNUM's 放入子查询来解决。
回答by David
Looks like a pagination query to me.
对我来说看起来像一个分页查询。
From this ASKTOM article (about 90% down the page):
来自这篇 ASKTOM 文章（页面下方约 90%）：
您需要对这些分页查询进行排序，以便每次都确定性地将 ROW_NUMBER 分配给行。
Also your queries are no where near the same so I'm not sure what the benefit of comparing the costs of one to the other is.
此外，您的查询也不尽相同，因此我不确定将一个与另一个的成本进行比较有什么好处。
回答by Gary Myers
Part of the problem is how big is the 'start' to 'end' span and where they 'live'. Say you have a million rows in the table, and you want rows 567,890 to 567,900 then you are going to have to live with the fact that it is going to need to go through the entire table, sort pretty much all of that by id, and work out what rows fall into that range.
部分问题在于“开始”到“结束”的跨度有多大以及它们“居住”在哪里。假设您在表中有 100 万行，并且您想要第 567,890 到 567,900 行，那么您将不得不接受这样一个事实，即它需要遍历整个表，按 id 对所有行进行排序，并计算出哪些行属于该范围。
In short, that's a lot of work, which is why the optimizer gives it a high cost.
简而言之，这是很多工作，这就是优化器给它高成本的原因。
It is also not something an index can help with much. An index would give the order, but at best, that gives you somewhere to start and then you keep reading on until you get to the 567,900th entry.
这也不是索引可以提供多大帮助的东西。一个索引会给出顺序，但充其量，它会给你一个开始的地方，然后你继续阅读，直到你到达第 567,900 个条目。
If you are showing your end user 10 items at a time, it may be worth actually grabbing the top 100 from the DB, then having the app break that 100 into ten chunks.
如果您一次向最终用户展示 10 个项目，实际上可能值得从数据库中抓取前 100 个项目，然后让应用程序将这 100 个项目分成 10 个块。
回答by Gandalf
Is your ORDER BY column indexed? If not that's a good place to start.
您的 ORDER BY 列是否已编入索引？如果不是，那是一个很好的起点。
回答by duffymo
Spend more time with the EXPLAIN PLAN tool. If you see a TABLE SCAN you need to change your query.
花更多时间使用 EXPLAIN PLAN 工具。如果您看到 TABLE SCAN，则需要更改查询。
Your query makes little sense to me. Querying over a ROWID seems like asking for trouble. There's no relational info in that query. Is it the real query that you're having trouble with or an example that you made up to illustrate your problem?
您的查询对我来说毫无意义。查询 ROWID 似乎是自找麻烦。该查询中没有相关信息。这是您遇到问题的真实查询还是您为说明问题而编写的示例？
