Oracle 10g中等价视图执行CONNECT BY层级查询结果不一致问题

问题产生原因 #

该现象是Oracle 10g版本中包含WITH子查询（通用表达式CTE）的视图在层级查询场景下的查询转换缺陷导致的，具体逻辑如下：

• Oracle查询优化器默认会对视图执行「视图合并（View Merging）」优化，会将视图的定义逻辑拆开，和外层查询的条件合并为一个整体生成执行计划，避免单独执行视图再做外层过滤的额外性能开销。
• 对于depemp2这类直接使用多表关联编写的视图，优化器的合并逻辑运行正常：外层的CONNECT BY条件会正确作用在视图展开后的字段上，能正常匹配v1_manager is null的起始行，再递归关联得到所有6条层级数据。
• 对于depemp1这类内部使用WITH子句定义的CTE视图，Oracle 10g的优化器在处理外层包含CONNECT BY的查询时，合并逻辑出现错误：它错误将CONNECT BY prior v1_empid = v1_manager的关联条件提前应用到了内部CTE的关联逻辑中，导致start with指定的v1_manager is null条件没有匹配到任何行，最终返回空结果。

你可以通过禁用视图合并的Hint验证该结论，执行以下语句就能得到和depemp2完全一致的查询结果：

select /*+ no_merge(depemp1) */
 level, 
 depemp1.*
  from depemp1
connect by prior v1_empid = v1_manager
 start with v1_manager is null

该缺陷在Oracle 11gR2及后续版本中已经被修复，更高版本中对两个视图执行相同层级查询的返回结果完全一致。

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