Oracle中ORDER BY为何比MAX函数性能更优？

这两个语句看似语义等价，但Oracle优化器生成的执行计划完全不同，导致性能天差地别，核心原因如下：

• 执行路径的本质区别
  对于SELECT col1 FROM table1 WHERE ROWNUM < 2 ORDER BY col1 DESC;，如果col1上存在B树索引，优化器会直接定位到索引的最右端（对应最大的col1值），取出第一条记录后立刻终止执行，不需要遍历全表或整个索引。而SELECT MAX(col1) FROM table1;在某些情况下，优化器可能选择全表扫描，或者即使走索引也会遍历所有索引条目来确认最大值（尤其是统计信息不准确时），这会导致大量IO和数据传输，远程连接下很容易超时。

• 统计信息过时
  Oracle优化器依赖表和索引的统计信息来选择执行计划。如果table1的统计信息长时间未更新，优化器可能误判MAX(col1)的最优路径，比如认为全表扫描的成本比走索引更低，而带ROWNUM的语句因为明确的行限制，优化器会优先选择高效的索引扫描路径。

• 索引可用性或优化器成本计算偏差
  如果col1上的索引存在碎片、被标记为不可用，或者优化器的成本模型计算有误（比如远程连接的网络成本未被正确评估），会导致MAX(col1)无法利用索引快速获取最大值，只能走全表扫描；而ORDER BY + ROWNUM的组合会强制优化器优先考虑排序效率，从而选择索引扫描。

• 远程连接的数据传输差异
  远程操作时，全表扫描需要将大量数据从数据库服务器传输到客户端，即使服务器端执行不算慢，网络传输延迟也会导致超时；而带ROWNUM的语句只返回一行数据，传输量极小，不会触发超时。

内容的提问来源于stack exchange，提问作者CaseH