Oracle 11g嵌套表关联子查询计算最大值时显示为空的异常问询

这是Oracle 11g中集合类型与关联子查询交互的已知优化bug，属于查询执行计划逻辑错误导致的集合数据丢失。

问题根源 #

Oracle 11g的查询优化器在处理collect()生成的嵌套表，同时存在关联子查询访问该嵌套表时，会错误地判定：来自真实空表左连接的v.p_id空值属性，与集合生成逻辑存在冲突；再加上union all中包含空结果集分支，触发了优化器的逻辑漏洞，最终导致原本应生成的集合被错误清空。

复现条件 #

• 仅在Oracle 11g（含11.2.0.1至11.2.0.4全版本）中复现，12c及以后版本已修复
• 必须涉及真实空表的左连接，用CTE/子查询模拟空表不会触发问题
• 左连接产生的空列不能是字面量null，必须来自真实表的字段
• union all中必须包含一个返回空结果集的分支

SQL层面解决方案 #

方案1：强制物化集合结果 #

使用materialize提示强制CTE物化，阻断优化器的错误改写逻辑：

with v (r_id, p_id) as (
  select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
  select v.r_id from dual join v on 0=1
  union all
  select v.r_id from dual join v on v.p_id is null
), w as (
  select /*+ materialize */ cast(collect(cast(u.r_id as number)) as rms.joedcn_number) as r_ids
  from u
)
select w.r_ids
     , (select max(column_value) from table(w.r_ids)) max_val
from w

方案2：替换关联子查询为直接聚合 #

在集合生成的同时计算最大值，避免后续访问嵌套表：

with v (r_id, p_id) as (
  select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
  select v.r_id from dual join v on 0=1
  union all
  select v.r_id from dual join v on v.p_id is null
)
select cast(collect(cast(u.r_id as number)) as rms.joedcn_number) as r_ids
     , max(u.r_id) as max_val
from u

方案3：修改空分支的连接逻辑 #

将返回空结果集的分支改为where 1=0写法，规避优化器的错误触发条件：

with v (r_id, p_id) as (
  select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
  select v.r_id from v where 1=0
  union all
  select v.r_id from dual join v on v.p_id is null
), w as (
  select cast(collect(cast(u.r_id as number)) as rms.joedcn_number) as r_ids
  from u
)
select w.r_ids
     , (select max(column_value) from table(w.r_ids)) max_val
from w

补充说明 #

迁移至Oracle 19c后该问题会自动消失，因为Oracle在12c版本重构了集合类型的执行逻辑，彻底修复了这类优化器bug。

内容的提问来源于stack exchange，提问作者Tomáš Záluský