为何关联子查询比窗口函数查询性能更优？

待分析的SQL语句 #

语句1 #

select tbraccd_pidm, 
       max(tbraccd_balance) 
       keep (dense_rank first order by tbraccd_tran_number) 
       over (partition by tbraccd_pidm)
from tbraccd;

语句2 #

select tbraccd_pidm, tbraccd_balance
from tbraccd a
where tbraccd_tran_number = (select max(tbraccd_tran_number)
                             from tbraccd b 
                             where b.tbraccd_pidm = a.tbraccd_pidm);

相关环境信息 #

• 索引：PK_TBRACCD 是基于 TBRACCD_PIDM 和 TBRACCD_TRAN_NUMBER 创建的主键索引
• 执行计划截图：
  • 语句1执行计划：
  • 语句2执行计划：
• 性能统计：根据V$Stats数据，第一条查询首次调用的CPU使用率是第二条的2.43倍，DB时间差异也基本一致

问题 #

两者均使用相同索引并执行全扫描，为何第二种查询性能更优？

性能差异原因分析 #

这两种查询虽然都基于PK_TBRACCD索引做全扫描，但内部执行逻辑的差异直接导致了性能差距：

1. 窗口函数的额外计算开销
   语句1的keep (dense_rank first...)结合窗口函数，数据库需要完成一系列CPU密集型操作：

• 按tbraccd_pidm分组后，对每组内的tbraccd_tran_number排序，筛选出"first"级别的记录
• 再针对这些记录计算tbraccd_balance的最大值
  整个过程需要在内存中维护分组、排序的中间状态，数据量越大，这类计算的CPU消耗就越明显。

2. 子查询利用索引有序性简化逻辑
   语句2的关联子查询充分发挥了主键索引的有序特性（PK_TBRACCD按tbraccd_pidm、tbraccd_tran_number排序）：

• 对于主查询的每条tbraccd_pidm，子查询可以直接定位到该分组下tbraccd_tran_number的最大值（同pidm的记录在索引中连续存储，最大值处于分组末尾）
• 后续直接通过该最大值匹配主查询记录，全程没有额外的排序、聚合操作，计算逻辑更直接，CPU消耗更低。

3. 执行计划的操作复杂度差异
   从执行计划能看出：

• 语句1包含窗口排序、聚合等额外步骤，这些都是CPU高消耗环节
• 语句2的计划以索引关联查找为主，IO开销与语句1相近，但避免了冗余计算，因此整体性能更优。

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