Oracle ROWNUM 性能

Question

Oracle ROWNUM 性能

8

为了查询Oracle中的前n行，通常使用ROWNUM。因此，以下查询似乎可以（获取最近的5个付款）：

select a.paydate, a.amount
from (
  select t.paydate, t.amount
  from payments t
  where t.some_id = id
  order by t.paydate desc
) a
where rownum <= 5;

但对于非常大的表格，这种方法效率很低 - 对我来说需要运行约10分钟。因此，我尝试了其他查询方式，最终找到了这个可以在不到1秒钟内运行的查询：

select *
from (
  select  a.*, rownum
  from (select t.paydate, t.amount
        from payments t
        where t.some_id = id
        order by t.paydate desc) a
)
where rownum <= 5;

为了了解发生了什么，我查看了每个查询的执行计划。对于第一个查询：

SELECT STATEMENT, GOAL = ALL_ROWS   7   5   175
COUNT STOPKEY           
VIEW    7   5   175
TABLE ACCESS BY INDEX ROWID 7   316576866   6331537320
INDEX FULL SCAN DESCENDING  4   6

其次：

SELECT STATEMENT, GOAL = ALL_ROWS   86  5   175
COUNT STOPKEY           
VIEW    86  81  2835
COUNT           
VIEW    86  81  1782
SORT ORDER BY   86  81  1620
TABLE ACCESS BY INDEX ROWID 85  81  1620
INDEX RANGE SCAN    4   81

显然，对于大表来说，第一个查询效率低下的原因是INDEX FULL SCAN DESCENDING。但是我无法通过观察两个查询来区分它们的逻辑。

有人能用人类语言解释一下这两个查询之间的逻辑差异吗？

提前感谢！

- Bazi

2

id 是一个绑定变量，不是 (应该是 :id ?) 如果是的话，使用什么值 (相同?) - tbone

2

我认为你在第二个版本中用于过滤的 rownum 不能保证与第一个版本中相同；你需要给第二个查询添加别名并引用它，或者在针对 a 的查询中添加 order by rownum？虽然我不认为这会影响速度。 - Alex Poole

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dave Costa · Accepted Answer

首先，如Alex的评论中所提到的，我不确定你的第二个版本是否百分之百地保证给出正确的行--因为查询的“中间”块没有明确的order by，Oracle没有义务以任何特定的顺序向外层查询块传递行。然而，在实践中，似乎没有任何特殊的原因会改变从最内部块传递行的顺序，因此它可能会起作用。

这就是为什么Oracle为第二个查询选择了不同的计划--在逻辑上无法将STOPKEY操作应用于最内部的查询块。

我认为在第一种情况下，优化器假设id值是良好分布的，并且对于任何给定的值，可能有一些非常近期的交易。由于它只需要找到最近的5个匹配项，它计算出看起来更有效的方法是使用索引按paydate的降序扫描行，查找相应的id和其他数据，并在找到第一个5个匹配项时停止。我怀疑您会看到针对此查询的性能非常不同，具体取决于您使用的特定id值-如果id有很多最近活动，则应该很快找到行，但如果没有，则索引扫描可能需要做更多的工作。

在第二种情况下，我认为它由于额外的嵌套层无法将STOPKEY优化应用于最内部块。在这种情况下，索引完全扫描将变得不那么有吸引力，因为它总是需要扫描整个索引。因此，它选择在id上进行索引查找（我假设），然后对日期进行实际排序。如果给定的id值匹配少量行，则这可能更有效-但如果您给出分散在整个表中的许多行，则我预计它会变慢，因为它将必须访问和排序许多行。

因此，我猜测你的测试使用了相对较少行而且不是很新的id值。如果这是一个典型的用例，那么第二个查询可能更适合您（再次强调，我不确定它是否在技术上保证产生正确的结果集）。但是，如果典型的值更有可能有许多匹配的行和/或更有可能有5个非常近期的行，则第一个查询和计划可能更好。