Oracle按最大日期筛选数据两种SQL写法对比及最优方案选择

两种写法的性能对比结论 #

大多数生产场景下你认为的第二种写法效果更优，核心原因和适用边界如下：

性能差异的核心逻辑 #

• 第一种WHERE嵌套关联子查询的本质是行级匹配：如果table1数据量大，每扫描table1的一行数据，就要触发一次table2的按id查最大日期的查询，相当于循环执行N次子查询（N等于table1的扫描行数），如果table1有几十万上百万行，这种执行模式的开销会非常高。就算Oracle优化器支持子查询展开，一旦统计信息不准就很容易生成低效执行计划。
• 第二种WITH预聚合+关联的逻辑是批量计算：先对table2做一次全量聚合，一次性算出所有table_id对应的最大日期，聚合后的结果集行数最多等于table2中不同table_id的数量，通常远小于table2原始行数，再和table1做关联的时候，不管走HASH连接还是嵌套循环，开销都低很多。如果table2上有(table_id, date_tmp)的联合索引，这个预聚合步骤可以直接走索引快速全扫描，不需要回表，性能还能再提升一个档次。

特殊场景例外 #

如果你的业务场景里table1的扫描行数极少（比如有过滤条件后只有几十行），而table2的数据量特别大，那第一种写法反而可能更快：它不需要全表扫table2做聚合，只需要查少量几个table_id对应的最大日期即可。

实操建议 #

日常开发优先选第二种写法，执行计划更可控，不容易出现统计信息不准导致的性能突降。验证性能可以直接用EXPLAIN PLAN FOR加两种SQL，对比逻辑读、执行时间的指标即可。

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