如何在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工具？

Answer 1

ROW_NUMBER的效率很低Oracle。

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

• 甲骨文：ROW_NUMBER与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放入子查询中来解决此问题。