Oracle数据库1=1关联单行小表查询异常缓慢的原因及解决方案

问题根因 #

Oracle 对 WITH 子句定义的 CTE（公共表表达式）默认采用视图合并策略，不会主动将 CTE 结果缓存为临时表：

• 直接执行 select * from inv 时，优化器仅需完成一次逻辑：对100万行的 inv_table 做全表扫描，分组聚合得到仅3行结果，整体耗时很短。
• 新增 join config on 1=1 后，优化器会对整个 SQL 做视图合并重写，把 inv、config 两个 CTE 的逻辑全部展开合并到主查询中。由于 1=1 是恒真的笛卡尔关联条件，优化器无法通过关联条件做过滤，会生成错误执行计划：要么将 inv_table 的全表扫描逻辑重复执行多次，要么把聚合操作延后到两次笛卡尔关联之后，原本只需要扫描1次100万行的操作变成了重复扫描、重复计算，最终耗时飙升到1小时以上。
  你用到的 /*+materialize */ 提示的作用就是强制 Oracle 将对应 CTE 的结果集物化为临时表，后续所有引用该 CTE 的逻辑都直接读取临时表的少量结果，完全避免了大表的重复扫描和计算，因此性能直接恢复正常。

解决方案 #

方案1：使用 materialize hint 强制 CTE 物化 #

就是你已经验证生效的写法，对于小结果集的 CTE 来说额外开销极低，效果直接：

with config as (
    select /*+materialize */
    ratio_A, ratio_B from config_table --该表仅1行
    ),
inv as(
    select /*+materialize */
    id, name, sum(qty) as qty
    from inv_table 
    join config on 1=1
    group by id, name
    )
select * from inv
join config on 1=1

方案2：改写 SQL 避免重复关联小表 #

因为 config 表只有1行，可以直接用标量子查询获取参数，完全不需要写两次关联逻辑：

select 
    id, name, sum(qty) as qty,
    (select ratio_A from config_table) ratio_A,
    (select ratio_B from config_table) ratio_B
from inv_table
group by id, name

该写法中标量子查询仅会执行一次，不会触发大表重复扫描的问题，性能和加hint的方案一致，写法更简洁。

方案3：优化小表统计信息辅助优化器判断 #

给仅1行的 config_table 增加主键约束，主动更新表的统计信息，帮助优化器识别到该表的极小数据量，避免生成错误的视图合并执行计划，无需修改业务查询逻辑即可解决问题。

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