Oracle中CTE与其他表关联时原基表的对应索引是否可被使用

你提到的a表colx索引是否能在ds与c表关联阶段被调用，完全取决于Oracle优化器对CTE的处理策略，没有固定的唯一答案，分两种核心场景：

场景1：优化器触发视图合并（View Merging） #

• 这是简单CTE场景下的默认处理逻辑：Oracle不会单独执行CTE里的查询，而是会把CTE的逻辑和外层查询拆平合并为一个整体的关联计划，等价于你直接执行如下SQL：

select * from table a
join table b on a.colx = b.coly
join table c on a.colx = c.colz

• 这种情况下a.colx的索引完全可以在ds与c关联的阶段被调用：比如优化器估算后选择c表作为嵌套循环外层驱动表，那么每遍历一行c表的数据，都会直接用c.colz的值去走a.colx的索引匹配a表的行，再关联b表做过滤，此时a.colx的索引就是在和c表的关联阶段生效的，不局限于a和b的关联阶段。

场景2：优化器选择物化CTE（CTE Materialization） #

• 当CTE包含聚合、DISTINCT、复杂子查询，或者成本估算认为单独生成CTE临时结果集成本更低，或者你手动加了/*+ MATERIALIZE */hint时，Oracle会先把CTE的查询结果物化到临时段（内存或临时表空间），此时的ds就是一个和基表完全独立的临时结果集。
• 这种情况下a.colx的索引仅会在a和b关联生成ds临时结果集的阶段被使用，后续ds和c表关联时，是临时结果集和c表做关联，已经和原来的a表索引没有关系，不会再调用a.colx的索引。

针对你给出的示例说明 #

你提供的CTE逻辑非常简单，没有聚合、去重等阻断视图合并的操作，绝大多数概率会触发视图合并，此时a.colx的索引可以在和c表的关联阶段被优化器调用。你可以通过执行EXPLAIN PLAN FOR加你的SQL，再查看执行计划确认，如果执行计划里没有出现TEMP TABLE TRANSFORMATION的步骤，就说明走了视图合并，索引可以跨CTE关联生效。

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