Oracle 11g生产环境内联视图关联SQ3触发ORA-01427错误，WITH子句改写后正常的原因咨询

这个问题的核心在于Oracle 11g优化器对内联子查询和WITH子句（子查询因子化）的执行策略差异——尽管你认为逻辑完全一致，但优化器的执行路径发生了本质变化，直接导致了报错与否的区别：

1. 内联子查询可能被优化器当作「相关子查询」执行 #

在你的原查询中，SQ3是作为左连接的直接数据源内联编写的。Oracle 11g的优化器可能会选择不预先执行这个子查询并生成独立结果集，而是将其「展开」（merge）到主查询的连接逻辑中，变成一个针对外部表每一行的相关子查询。

也就是说，优化器会针对t1/t2的每一行，去执行一次SQ3的子查询来匹配连接条件。这时候，虽然你的SQ3里有GROUP BY和MAX()，但当和外部表的连接条件（比如sq3.c3=t1.c3等）结合后，可能会出现某一行外部数据对应的子查询返回了多行结果——这就触发了ORA-01427错误。

2. WITH子句强制子查询「物化」为临时结果集 #

当你用WITH子句把SQ3抽出来后，Oracle 11g默认会先执行这个子查询，将结果物化到一个临时的内存/磁盘表中，然后再用这个物化后的结果集和其他表做左连接。

这时候，SQ3的GROUP BY和MAX()逻辑是独立执行的，生成的结果集本身已经保证了(c1,c2,c3,c4,c5,c6)的唯一性（因为GROUP BY了这些字段）。后续的左连接只是基于这个预先生成的唯一结果集做匹配，自然不会出现单行子查询返回多行的情况。

3. 11g优化器的行为特性 #

Oracle 11g的优化器在处理WITH子句时，默认倾向于物化子查询（除非它判断物化会带来性能损耗）；而内联子查询则更可能被合并到主查询中，导致执行逻辑和你预期的独立子查询不一致。

验证方式 #

你可以对比两个查询的执行计划：

• 原查询的执行计划中，可能看不到独立的SQ3执行步骤，而是会有和主表关联的子查询执行逻辑；
• WITH版本的执行计划中，会明确显示WITH SUBQUERY MATERIALIZED的标记，说明SQ3已经被预先执行并物化。

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