sql - 如何在 Oracle 中加速 row_number？

Question

我有一个看起来像这样的 SQL 查询:

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

从本质上讲，是 ORDER BY 部分减慢了速度。如果我要删除它，解释成本会下降一个数量级(超过 1000 倍)。我试过这个:

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

但这并没有给出正确的结果。有什么简单的方法可以加快速度吗？还是我需要花更多时间使用 EXPLAIN 工具？

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 来解决。进入子查询。