如何在Oracle中加快row_number的速度? [英] How can I speed up row_number in Oracle?

查看:248
本文介绍了如何在Oracle中加快row_number的速度?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个类似于以下内容的SQL查询:

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

从本质上讲,是ORDER BY部件在减慢速度.如果我要删除它,则EXPLAIN成本会下降一个数量级(超过1000倍).我已经尝试过了:

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

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

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?

推荐答案

ROW_NUMBEROracle中效率很低.

有关性能的详细信息,请参阅我博客中的文章:

See the article in my blog for performance details:

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

此查询将使用COUNT STOPKEY

还请确保您column不可为空,或添加WHERE column IS NOT NULL条件.

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.

请注意,没有子查询就不能使用ROWNUM BETWEEN :start and :end.

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

ROWNUM总是最后分配并最后检查,这就是ROWNUM总是无间隙排列的方式.

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

如果使用ROWNUM BETWEEN 10 and 20,则满足所有其他条件的第一行将成为返回的候选对象,并临时分配给ROWNUM = 1,并且未通过ROWNUM BETWEEN 10 AND 20的测试.

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.

然后,下一行将是候选行,分配给ROWNUM = 1并失败,依此类推,因此,最后,将根本不返回任何行.

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

这可以通过将ROWNUM放入子查询中来解决.

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

这篇关于如何在Oracle中加快row_number的速度?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆