如何在Oracle中加速row_number？

Question

我有一个类似于这样的 SQL 查询：

SELECT * FROM(
    SELECT
        ...,
        row_number() OVER(ORDER BY ID) rn
    FROM
        ...
) WHERE rn between :start and :end

基本上，是ORDER BY这一部分使事情变慢。如果我将其删除，EXPLAIN成本将下降一个数量级（超过1000倍）。我尝试过这样做：

SELECT 
    ...
FROM
    ...
WHERE
    rownum between :start and :end

但是这样做并不能得出正确的结果。有没有简单的方法可以加速它？或者我需要花更多时间使用EXPLAIN工具吗？

- Jason Baker

Answer 1

ROW_NUMBER 在 Oracle 中效率较低。

有关性能详细信息，请参阅我的博客文章：

• Oracle: ROW_NUMBER vs ROWNUM

对于您的特定查询，建议您将其替换为 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

这个查询将使用COUNT STOPKEY

还要确保column不可为空，或添加WHERE column IS NOT NULL条件。

否则索引无法用于检索所有值。

请注意，您不能在没有子查询的情况下使用ROWNUM BETWEEN :start and :end。

ROWNUM总是最后分配和最后检查，这就是为什么ROWNUM始终按顺序而不间断地出现的原因。

如果您使用ROWNUM BETWEEN 10 and 20，则满足所有其他条件的第一行将成为返回的候选行，临时分配ROWNUM = 1并失败于ROWNUM BETWEEN 10 AND 20的测试。

然后下一行将成为候选行，并分配ROWNUM = 1并失败等等，因此最终根本不会返回任何行。

应该通过将ROWNUM放入子查询中来解决这个问题。

- Quassnoi

---

运行得非常好。然而，优化提示似乎没有产生明显的差异。 - Jason Baker

这意味着CBO足够聪明，能够选取索引。在这里真正起作用的是ROWNUM而不是ROW_NUMBER。 - Quassnoi

但是我仍然会留下提示或创建一个概述，以防CBO改变主意 :) - Quassnoi

顺便说一下，我还没有尝试过两种方法，但如果我使用FIRST_ROWS而不是INDEX_ASC和NOPARALLEL_INDEX来查询，解释成本将从大约25,000降至8，并且运行时间几乎保持恒定（我可以像拉一个记录一样快地拉取所有记录），所以我可能甚至不需要再分页了。也许在记录计数突然激增的情况下，我仍然会使用分页。 - Jason Baker

+1 但我遇到了一些困难才使其正常工作。我不得不使用上述博客文章http://explainextended.com/2009/05/06/oracle-row_number-vs-rownum/中的最后一个示例（带有两个子查询）。 - Maxime Pacary

Answer 2

我认为这是一个分页查询。

根据ASKTOM文章（页面下方约90％）：

您需要按照某些唯一的条件进行排序，以便对于每个行，都能确定地分配ROW_NUMBER。

此外，您的查询远非相同，因此我不确定将一个查询的成本与另一个查询进行比较的好处在哪里。

- David

---

实际上，那篇文章帮助我编写了查询语句。不过我没有注意到按照唯一ID排序的部分。还有一个查询优化提示我也错过了。明天我会在工作中尝试一下！ - Jason Baker

;) 看起来很熟悉。使用分页查询时，first_rows 可以是一个很棒的选择。 - David

那个Quassnoi的建议让我的查询时间几乎达到了常数级别！但我希望我能选择两个答案。 :-( - Jason Baker

Answer 3

你的 ORDER BY 列有索引吗？如果没有，那就是一个好的起点。

- Gandalf

---

事实上，这并不是问题所在。但将其更改为已经建立索引的行并没有帮助。尽管如此，还是感谢您提出了显而易见的建议。 :-) - Jason Baker

只有当访问路径可以使用索引（即您正在查找一系列 ID）时，索引才会帮助改善 ORDER BY。 - Dave Costa

Answer 4

问题的一部分是“开始”到“结束”的跨度有多大，以及它们在哪里。 假设您的表中有一百万行数据，并且您想要从第567,890行到第567,900行，则必须接受这样一个事实：需要遍历整个表，按id排序，然后计算出哪些行落入该范围内。

简而言之，这是很多工作，这就是为什么优化器会给它高成本的原因。

这也不是索引可以帮助解决的问题。索引可以提供顺序，但最好的情况下，它只能让您找到一个起点，然后继续读取直到达到第567,900个条目。

如果您每次向最终用户显示10个项目，那么从数据库中获取前100个项目，然后让应用程序将这100个项目分成十个块可能更值得考虑。

- Gary Myers

---

这听起来很合适。我需要从大约200万条记录中提取大约15,000条记录。我们的查询时间有限，一次性获取所有15k条记录会导致超时。因此，我认为通过分页显示结果可以避免这种情况。我想这意味着我必须经历请求更长超时时间的官僚噩梦。 - Jason Baker

希望你不会将15000行发送给用户！ - John Saunders

Answer 5

花更多时间使用EXPLAIN PLAN工具。如果你看到一个TABLE SCAN，你需要改变你的查询。

你的查询对我来说没有太大意义。在ROWID上查询似乎是在自找麻烦。这个查询中没有任何关系信息。这是你真正遇到问题的查询还是你为了说明问题而编造的例子？

- duffymo

---

这是分页。至少在分页方面，这基本上就是查询所做的事情。我刚刚删除了查询的其余部分（主要是因为它不是微不足道的）。所有省略号都是我为了简洁而删减的内容。 - Jason Baker