GBase 8a CTE 与派生表：优化器处理路径与可验证的改写方法

公用表表达式（CTE，WITH 子句）与派生表（子查询出现在 FROM/JOIN 中）在单机数据库中多用于提升可读性。在 GBase 8a 的 MPP 执行模型下，同一 SQL 的写法差异会改变 子查询是否物化、是否在分片间重复扫描、以及后续 Join 是否引入 REDISTRIBUTE。本文从执行计划可观测项出发，说明常见处理路径、误判统计信息时的表现，以及经 EXPLAIN 验证的改写顺序。命令与系统视图名称以现场版本文档为准。

一、问题定义与适用边界

典型场景包括：多层 WITH 嵌套、派生表与大事实表 Join、在 CTE 内先做聚合再关联维表、以及将过滤条件写在 CTE 外层导致谓词无法下推。症状通常表现为：逻辑行数不大但执行时间长；EXPLAIN 中出现多次对同一基表的 Table Scan；或在 Join 前出现 REDISTRIBUTE，估算行数与 SELECT COUNT(*) 抽样结果数量级不一致。

以下讨论默认事实表为 HASH 分布，维表规模远小于事实表。复制表（REPLICATED）仅在小表前提下使用，避免误将大表设为复制导致网络与内存压力上升。

二、CTE 的两种逻辑角色

从优化器视角，CTE 在逻辑计划中可能呈现两类角色（具体实现因版本而异，需以 EXPLAIN 为准）：

角色	计划特征（常见）	对性能的影响
内联（合并）	CTE 引用处直接展开，与外层谓词同一扫描边界	有利于过滤下推，减少中间结果物化
物化（落临时结果）	出现临时表、Sort、或独立 Gather 后再 Join	增加磁盘与网络；多次引用 CTE 时可能重复物化

判断方法：对原 SQL 与改写 SQL 分别 EXPLAIN，对比是否新增 Temp Table、Materialize 类算子（名称以版本输出为准），以及基表扫描次数是否减少。

多次引用同一 CTE（WITH x AS (...) SELECT ... FROM x JOIN x）时，部分版本会对 x 物化两次；若两次引用条件不同，内联合并可能失效。此时可改为：单次聚合写入 staging 表（分布键与下游 Join 键一致），外层两次读取 staging，通过 ANALYZE TABLE staging 保证基数估计准确。

三、派生表与谓词下推

派生表写法示例逻辑结构：FROM (SELECT ... FROM fact WHERE dt = ?) t JOIN dim ON ...。优化器若不能将 dt = ? 下推到 fact 的分区/分片扫描，则每个数据节点可能扫描超出必要范围的数据，再在派生表边界过滤。

下推失效的常见原因：

1. 派生表 SELECT 列表含非确定性函数或外层包裹函数，导致谓词无法等价迁移。
2. 派生表内 GROUP BY 键与外层过滤列无包含关系，优化器判定需先聚合后过滤。
3. 派生表与外层 Join 键和 fact 分布键不一致，计划在 Join 前插入 REDISTRIBUTE，掩盖了下推收益。

验证步骤：

1. 对原 SQL 执行 EXPLAIN，记录 fact 扫描算子上的过滤条件与估算行数。
2. 将日期、状态等选择性高的条件移入派生表内部 WHERE，再次 EXPLAIN。
3. 若估算行数与分片抽样仍偏差大，对 fact 执行 ANALYZE TABLE 后重复对比。

在分区表上，应优先保证分区键出现在派生表内部谓词中，以便触发 分区裁剪；仅在外层对派生表列过滤，在部分计划形态下无法替代分区级裁剪。

四、相关子查询与半连接改写

相关子查询（WHERE EXISTS (SELECT 1 FROM ... WHERE outer.key = inner.key)）在基数估计偏低时，可能被译为 NestLoop 或对内外表多次重复扫描。在 8a 上可优先评估下列等价改写是否降低 Motion：

原形态	改写方向	注意点
EXISTS	INNER JOIN（半连接）	Join 键与分布键对齐，避免双侧大 REDISTRIBUTE
NOT EXISTS	LEFT JOIN ... IS NULL	注意 NULL 语义与重复行
标量子查询	预聚合派生表再 Join	派生表按 Join 键分布或复制极小维表

改写后必须对比 Join 顺序、Motion 类型、估算行数 三项，不能仅比较语法等价。

五、聚合位置对 Motion 的影响

在 CTE 内对事实表按业务键 GROUP BY 再 Join 维表，通常优于「先 Join 再聚合」，原因是 Join 前行数下降，后续 REDISTRIBUTE 的字节数减少。前提条件：

1. GROUP BY 键与事实表分布键一致，或聚合可在各分片本地完成后再汇总。
2. 聚合后行数确实显著小于原表；若 GROUP BY 键接近唯一，收益有限。
3. 维表 Join 键与聚合键一致，或维表为复制表且行数在可接受范围内。

若 GROUP BY 键与分布键不一致，计划可能在聚合后出现 Gather + REDISTRIBUTE，需在 EXPLAIN 中单独评估该段代价，必要时调整分布键设计（属 DDL 级变更，需维护窗口）。

六、统计信息与计划可信度

CTE 与派生表场景下，优化器对中间结果行数估计误差会被放大。下列信号提示应优先收集统计信息，而非立即改 SQL：

• EXPLAIN 中某算子估算行数为 1 或极小，实际为百万级。
• 广播（BROADCAST）算子作用于行数上万的表。
• 同一基表在计划树中出现多次全表扫描，且估算行数彼此矛盾。

处理顺序建议：

1. 对基表、staging 表执行 ANALYZE TABLE（选项以文档为准）。
2. 在业务低峰重复 EXPLAIN，保存前后计划文本。
3. 若估计仍偏差大，再采用 staging 表固定中间基数，或拆分 SQL 为两步 ETL。

七、分步诊断流程（可执行）

1. 记录 SQL 文本、会话 database()、涉及表 DDL（含 DISTRIBUTED BY、分区定义）。
2. EXPLAIN 原 SQL，标注：基表扫描次数、REDISTRIBUTE/BROADCAST 位置、Gather 位置。
3. 对高选择性条件做派生表/CTE 内部下推 试验，对比扫描估算行数。
4. 评估聚合能否前移到 Join 之前；检查 GROUP BY 键与分布键关系。
5. 对 EXISTS/标量子查询尝试半连接或预聚合 Join，对比 Motion 变化。
6. 执行 ANALYZE 后重复步骤 2–5。
7. 若仍不达标，采用 staging 表（分布键对齐）+ ANALYZE + 外层简单 Join。
8. 记录最终计划与生产耗时，归档为案例条目。

八、案例说明（计划形态对比）

场景：事实表 fact_evt 按 user_id HASH 分布，单日过滤后约两千万行；需在用户维度聚合后关联省份维表 dim_prov（约三千行，未复制）。

原 SQL 结构：外层过滤 dt，CTE 内对 fact_evt 全表聚合，再 Join dim_prov；EXPLAIN 显示 CTE 分支对 fact_evt 全分片扫描且未下推 dt，聚合后 REDISTRIBUTE on user_id，维表侧 BROADCAST。

调整：

1. 将 dt 条件移入 CTE 内部，扫描估算行数下降至单日量级。
2. 将 dim_prov 改为 REPLICATED（经容量评估批准），消除 Join 前对维表侧的 REDISTRIBUTE。
3. 对 fact_evt、dim_prov 执行 ANALYZE TABLE。

结果形态：fact_evt 扫描带分区/谓词裁剪；本地聚合；Join 无双侧大 REDISTRIBUTE；总耗时与临时空间占用均下降。该案例说明：CTE 本身不是性能瓶颈，谓词位置、分布键对齐、统计信息 三者共同决定计划质量。

九、与现有运维文档的衔接

• 子查询专项改写可与「子查询与派生表改写」一文对照，避免重复引入多余 Motion。
• 统计信息收集周期、加载后 ANALYZE 要求，遵循统计信息专题中的维护策略。
• 若 EXPLAIN 已合理但 DN 级 IO 仍高，继续排查 数据倾斜 与 临时目录 spill，不在 CTE 层强行叠床架屋。

十、维护与规范建议

1. 生产 SQL 评审清单增加一项：CTE/派生表是否阻碍谓词下推、是否多次扫描同一基表。
2. 对常驻报表 SQL 保留「基线 EXPLAIN」文本，变更 DDL 或大批量加载后强制 diff。
3. staging 表命名与生命周期明确，避免无 ANALYZE 的临时表被后续任务 Join。
4. 禁止在未经计划验证的情况下，将多层 WITH 仅作格式整理视为无成本操作。

小结

在 GBase 8a 中，CTE 与派生表的核心风险在于：中间结果物化、谓词下推失败、分布键与 Join 键不一致导致的 REDISTRIBUTE。处理时应以 EXPLAIN 为据，按「下推过滤 → 聚合前置 → 分布键/复制表对齐 → ANALYZE → staging 固化基数」的顺序迭代；避免在未核对估算行数的情况下依赖语法等价改写。将每次调整的计划差异与耗时写入变更记录，便于后续版本升级或统计信息策略变更时回溯。