Oracle ROWNUM性能
要在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分钟。 所以我尝试了其他的疑问，最后我发现了一个不到一秒钟的问题：
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 ，它使大表首次查询效率低下。 但我无法通过查看它们来区分两个查询的逻辑。 任何人都可以解释两种人类语言查询之间的逻辑差异吗？
提前致谢！
首先，正如Alex的评论中提到的，我不确定你的第二个版本是否100％保证给你正确的行 - 因为查询的“中间”块没有明确的order by ，Oracle是没有义务将行按任何特定顺序传递给外部查询块。 但是，似乎并没有什么特别的理由可以改变行从最里面的块传递的顺序，所以实际上它可能会起作用。
这就是为什么Oracle为第二个查询选择不同的计划 - 逻辑上不能将STOPKEY操作应用于最内层的查询块。
我认为在第一种情况下，优化器假定id值分布很好，对于任何给定的值，可能会有一些非常近期的事务。 由于它可以看到它只需要查找最近5次匹配，因此它计算出使用索引按paydate降序扫描行，从表中查找相应的id和其他数据似乎更有效，并且当前5场比赛中发现时停止。 我怀疑你会发现这个查询的性能非常不同，具体取决于你使用的具体的id值 - 如果这个id有很多最近的活动，应该很快找到这些行，但是如果它没有，那么索引扫描可能需要做更多的工作。
在第二种情况下，我认为由于嵌套的额外层，无法将STOPKEY优化应用于最内部的块。 在这种情况下，索引全扫描将变得不太吸引人，因为它总是需要扫描整个索引。 因此，它选择对id进行索引查找（我假设），然后在日期进行实际排序。 如果给定的id值匹配行的一小部分，这可能会更有效率 - 但如果你给一个id遍布整个表的行数很多，我预计它会变慢，因为它会有访问和排序许多行。
所以，我猜测你的测试使用了行数相对较少的id值，这些行不是最近的。 如果这将是一个典型的用例，那么第二个查询对你来说可能更好（再一次，我不确定它在技术上保证产生正确的结果集）。 但是如果典型值更可能有许多匹配的行和/或更可能有5个最近的行，那么第一个查询和计划可能会更好。