如何加快 Oracle 中的 row_number 速度？

发布于 2024-07-18 04:44:14 字数 434 浏览 6 评论 0原文

我有一个看起来像这样的 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 工具吗？

原文

I have a SQL query that looks something like this:

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:

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

油焖大侠 2024-07-25 04:44:14

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

另外，请确保您的列 不可为空，或者添加WHERE 列IS NOT NULL 条件。

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

请注意，如果没有子查询，则无法使用 ROWNUM BETWEEN :start and :end。

ROWNUM 总是最后分配并最后检查，这样 ROWNUM 总是按顺序排列，没有间隙。

如果使用ROWNUM BETWEEN 10 and 20，则满足所有其他条件的第一行将成为返回的候选行，临时分配ROWNUM = 1并无法通过<的测试代码>ROWNUM 10 和 20 之间。

然后下一行将是候选行，分配有 ROWNUM = 1 并失败，等等，所以最后根本不会返回任何行。

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

ROW_NUMBER is quite inefficient in Oracle.

See the article in my blog for performance details:

Oracle: ROW_NUMBER vs ROWNUM

For your specific query, I'd recommend you to replace it with ROWNUM and make sure that the index is used:

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

Also either make sure you column is not nullable, or add WHERE column IS NOT NULL condition.

Otherwise the index cannot be used to retrieve all values.

Note that you cannot use ROWNUM BETWEEN :start and :end without a subquery.

ROWNUM is always assigned last and checked last, that's way ROWNUM's always come in order without gaps.

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 = 1 and fail the test of ROWNUM BETWEEN 10 AND 20.

Then the next row will be a candidate, assigned with ROWNUM = 1 and fail, etc., so, finally, no rows will be returned at all.

This should be worked around by putting ROWNUM's into the subquery.

回复收藏 0 原文