sql - Oracle ROWNUM 性能

Question

查询前-n Oracle中的行，一般使用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;

但是对于非常大的 table ，它效率低下 - 对我来说它运行了大约 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 , Oracle 没有义务以任何特定顺序将行向上传递到外部查询块。但是，似乎没有任何特殊原因可以改变行从最里面的块向上传递的顺序，因此在实践中它可能会起作用。

这就是 Oracle 为第二个查询选择不同计划的原因——它在逻辑上无法应用 STOPKEY操作到最里面的查询块。

我认为在第一种情况下，优化器假设 id值分布良好，对于任何给定的值，都可能有一些最近的交易。由于可以看到只需要找到最近的5个匹配项，所以计算出按照paydate的降序扫描行似乎效率更高。使用索引，从表中查找相应的 id 和其他数据，并在找到前 5 个匹配时停止。我怀疑您会看到此查询的性能非常不同，具体取决于您使用的特定 id 值——如果 id 最近有很多事件，应该很快找到行，但如果没有，则索引扫描可能需要做更多的工作。

在第二种情况下，我认为它不能应用 STOPKEY由于额外的嵌套层，对最里面的块进行了优化。在这种情况下，索引全扫描将变得不那么有吸引力，因为它总是需要扫描整个索引。因此它选择在 id 上进行索引查找。 (我假设)后跟日期的实际排序。如果给定 id value 匹配一小部分行，这可能更有效——但如果你给出 id有很多行分布在整个表中，我希望它变得更慢，因为它必须访问和排序许多行。

所以，我猜你的测试使用了 id行数相对较少且不是最近的值。如果这将是一个典型的用例，那么第二个查询可能更适合您(同样，需要注意的是，我不确定在技术上是否能保证产生正确的结果集)。但是如果典型值更有可能有许多匹配的行和/或更有可能有 5 个最近的行，那么第一个查询和计划可能会更好。