Oracle ROWNUM性能

Question

要查询中的行，一般使用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分钟。因此，我尝试了其他的查询，最后得到了一个运行时间不到一秒钟的查询：

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  

显然，是索引全扫描降序使得第一次查询大表效率低下。但是，我不能通过查看两个查询来真正区分它们的逻辑。有人能解释一下人类语言中两个查询之间的逻辑差异吗？

提前感谢！

Answer 1

首先，正如亚历克斯的评论中提到的，我不确定您的第二个版本是否能100%地保证给您正确的行--因为查询的“中间”块没有显式的order by，所以OracleOracle没有义务以任何特定的顺序将行传递到外部查询块。但是，似乎没有任何特殊的原因来改变从最里面的块中传递行的顺序，因此在实践中它可能会工作。

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

我认为在第一种情况下，优化器假设id值分布良好，对于任何给定的值，可能存在一些最近的事务。因为它可以看到它只需要查找最近的5个匹配项，所以它计算出使用索引按照paydate的降序扫描行、从表中查找相应的id和其他数据并在找到前5个匹配时停止似乎更有效。我怀疑，根据您使用的特定id值，您会看到这个查询的性能非常不同--如果id有很多最近的活动，那么应该很快找到行，但是如果没有，索引扫描可能需要做更多的工作。

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

因此，我猜想您的测试使用了id值，该值具有相对较少的行，而这些行并不是最近的。如果这是一个典型的用例，那么第二个查询可能对您更好(同样，我不确定它在技术上是否能够生成正确的结果集)。但是，如果典型的值更有可能有许多匹配的行和/或更可能有5个最近的行，那么第一个查询和计划可能会更好。