含CTE多子查询SQL在Oracle与PostgreSQL的执行计划及优化逻辑

待分析SQL #

with cte as (
   select
     sum(amount) sum_amount
   , count(*) my_count
   , orderid
   from order_details
   group by orderid
)
select 
  order.orderid
, (select sum_amount from cte where cte.orderid = order.orderid)
, (select my_count from cte where cte.orderid = order.orderid)
, (select orderid from cte where cte.orderid = order.orderid)
from order; 

PostgreSQL 执行计划与实现逻辑 #

PostgreSQL 默认不会将 CTE 结果物化，而是默认把 CTE 作为内联视图展开，目前优化器无法识别 3 个关联子查询引用的是同一 CTE 的同一条匹配行，执行逻辑如下：

• 外层遍历order表的每一行数据
• 对每一行的orderid，分别执行 3 次关联子查询：每次都会重新执行 CTE 内部逻辑（扫描order_details表、按orderid分组聚合），再匹配对应orderid返回目标字段
• 若order表有N行、order_details表有M行，总扫描量最高可达到N*3*M，执行效率极低
  如果需要在 PostgreSQL 中避免重复执行，可以手动添加MATERIALIZED关键字强制 CTE 物化：with cte as materialized (select ...)，此时 CTE 只会执行一次，结果存储在临时结构中，3 个子查询统一从临时结构匹配数据即可。

Oracle 执行计划与实现逻辑 #

Oracle 优化器（CBO）会自动识别重复的关联子查询和 CTE 引用，不会出现重复执行的情况，执行逻辑如下：

• 第一步先执行 CTE 逻辑：扫描order_details表，按orderid分组聚合得到所有orderid对应的sum_amount、my_count，将结果物化为临时结果集
• 第二步将外层order表和 CTE 临时结果集做左连接（关联子查询无匹配时返回 null，等价于左连接行为），一次关联即可取出 3 个目标字段，不需要执行 3 次子查询
• 执行计划中会出现TEMP TABLE TRANSFORMATION操作，原 SELECT 列表中的 3 个关联子查询会被优化器改写为等价的连接逻辑，不会出现在最终执行计划中。

Oracle 避免 CTE 重复执行的核心机制 #

Oracle CBO 优化器靠两个核心特性实现该优化：

1. 子查询去重与折叠：优化器会识别出 SELECT 列表中 3 个关联子查询的关联条件完全一致，都是cte.orderid = order.orderid，会自动把 3 个返回不同字段的子查询折叠为一次关联操作，避免多次匹配。
2. CTE 自动物化与公共表达式复用：对于被多次引用的 CTE，Oracle 会自动判断物化成本，只要引用次数超过 1 次，且物化成本低于内联重复执行的成本就会触发物化，CTE 结果只会生成一次，所有引用位置都复用该临时结果集，不需要重复执行 CTE 内部的聚合逻辑。
   除此之外Oracle还支持标量子查询缓存特性，就算未触发 CTE 物化，相同入参的标量子查询结果也会被缓存，相同orderid的 3 次查询只会执行一次。

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