直接查询CTE比最终UNION输出慢20倍？Oracle SQL性能疑问

背景与SQL结构 #

需要查询约27列数据，因存在子查询，将代码拆分为多个关联CTE，结构如下：

with CTE01 as (
  -- 员工基础行数据
),
CTE04 as (
  -- 该模块下员工信息行
),
CTE04_SUM as (
 -- 该模块下员工工时统计
),
CTE10 as (
  -- 同员工的其他模块信息行
),
CTE10_Sum as ( 
  -- 其他模块下员工工时统计
),
-- 合并CTE04和CTE10的统计到单行总记录，目前代码有bug：用CTE04左全连接CTE10，导致当CTE04无对应ID时，CTE10的总记录丢失
Quarter_Total as (
  -- 合并后的员工季度工时
) -- 整合CTE10_Sum和CTE04_Sum为单行记录

-- 基于Quarter_Total汇总年度总工时
YEAR_TOTAL as (
  -- 员工年度工时总计
)

-- 拼接所有结果集
select * from CTE04
union
select * from CTE04_SUM
union
select * from CTE10
union 
select * from CTE10_Sum
union 
select * from Quarter_Total
union
select * from YEAR_TOTAL

索引配置情况 #

涉及的表理论上已建立索引，通过PL/SQL Developer的图表窗口确认过索引列，并基于这些列关联表，但不排除操作失误。

异常性能表现 #

• 单独查询单个CTE（如CTE04）耗时约15秒，部分CTE甚至需要100-200秒；但执行最终的UNION拼接查询仅需5-6秒
• 最终UNION的结果已自动排序，而单独查询CTE的结果未排序，却性能极差
• 所有CTE的数据量均不足2000行

疑问点 #

1. 猜测是Oracle SQL优化器的作用让最终查询性能优异，但无法理解为何性能差异如此巨大
2. 曾将某CTE中WHERE条件的单列移至独立CTE并关联，性能有所提升，但SQL是声明式语言，疑惑为何手动调整会生效，而非由优化器自主决策

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