TEMP TABLE TRANSFORMATION 改写导致查询性能劣化

问题现象

用户执行含多个相似子查询的 SQL 时，查询性能从预期的毫秒级急剧退化至秒级甚至无法返回，但将 SQL 中的子查询拆开单独执行时，各子查询均能快速返回（毫秒级）。 典型操作场景与触发方式：

• 场景 A（标量子查询引用同一视图）：SQL 中存在两个引用相同视图的标量子查询，且子查询含关联谓词（如 WHERE id = t.fk_id）。执行该 SQL 后，原本通过主键/索引定位单行的操作退化为对视图全量数据的扫描。例如：

-- 业务查询：两个子查询均引用视图 vw_iqc_exa_standards
SELECT
  (SELECT version FROM vw_iqc_exa_standards WHERE id = ei.common_standards_id),
  (SELECT version FROM vw_iqc_exa_standards WHERE id = ei.special_standards_id)
FROM exam_item ei WHERE ei.batch_id = '...';
-- 实际耗时 67 秒，返回 10 行；视图底层 UNION ALL 共 630 万行

• 场景 C（CASE WHEN 多分支嵌套子查询）：CASE WHEN 各分支包含结构相似但关联条件不同的嵌套子查询（引用相同大表），优化器将各分支子查询合并物化，原本可走主键的查询退化为全量 HASH JOIN：

SELECT CASE
  WHEN (SELECT TRANSCODE FROM ACCT_TRANSACTION WHERE serialno = '...') IN ('0055','0040')
  THEN (SELECT ... FROM ... WHERE at.serialno = '...' ...)   -- 可走 serialno 主键
  WHEN (SELECT TRANSCODE FROM ACCT_TRANSACTION WHERE serialno = '...') IN ('4020','4030')
  THEN (SELECT ... FROM ... ...)
END FROM dual;
-- 实际代价 125 亿，拆分后代价 170，耗时从数十秒降至毫秒

• 场景 D（OR 展开触发二次抽取）：WHERE 子句含多个 OR ... IN (子查询) 分支，同时存在外层 NOT EXISTS 子查询，OR Expansion 将 OR 拆为 UNION ALL 后，NOT EXISTS 在各分支重复出现，进而被抽取为 TEMP TABLE。 共同问题表现：
• EXPLAIN 输出中 0 号算子为 TEMP TABLE TRANSFORMATION，计划中存在 TEMP TABLE INSERT 和 TEMP TABLE ACCESS 算子。
• EXPLAIN FORMAT=EXTENDED 输出中，TEMP TABLE INSERT 算子的 REAL.ROWS 远大于最终输出行数（例：物化 630 万行，最终返回 10 行）；TEMP TABLE ACCESS 算子的 REAL.TIME 远大于 EST.TIME（例：实际 34s，估算 1μs）。
• GV$OB_SQL_PLAN_MONITOR 中，TEMP TABLE INSERT 算子的 output_rows 字段可观察到同样的物化量级。

关键诊断信息

触发条件

满足以下任一场景时，可能触发 TEMP TABLE TRANSFORMATION 误抽取导致查询性能劣化：

• 场景 A：SQL 中存在两个及以上引用相同视图/表的标量子查询，且子查询含关联谓词（如 WHERE id = t.fk_id）。
• 场景 B：子查询引用 UNION ALL 大视图，物化须扫描所有分支基表，各分支条件无法穿透。
• 场景 C：CASE WHEN 各分支包含结构相似但关联条件不同的嵌套子查询，各分支被合并为一个公共子计划，条件失去下推路径。
• 场景 D：WHERE 子句含多个 OR ... IN (子查询) 分支，同时存在外层 NOT EXISTS 子查询，OR 展开后 NOT EXISTS 在各分支重复出现，再次触发抽取。
• 场景 E：多 CTE 中存在无过滤条件的全量聚合 CTE。 版本相关：OBServer 3.x 与 4.x（低于 4.2.5）版本更易触发误抽取；4.2.5 及之后版本大部分场景可自动规避。

事前巡检

检查 TEMP TABLE 抽取是否已被全局关闭（若已关闭，则问题另有原因）：

-- 查看 TEMP TABLE 抽取是否已被关闭
SHOW PARAMETERS LIKE '_xsolapi_generate_with_clause';
-- 默认开启（value 为 True 或 1）；若已设为 False 或 0，说明已全局关闭

事后诊断

1. 核心判断：执行计划特征 通过 EXPLAIN 或 EXPLAIN EXTENDED 获取执行计划，确认以下特征：

-- 获取执行计划
EXPLAIN EXTENDED <problem_sql>;
-- 或从 plan cache 获取
SELECT plan_id, query_sql, plan_type
FROM oceanbase.gv$ob_plan_cache_plan_stat
WHERE tenant_id = <tenant_id>
  AND query_sql LIKE '%<关键表名>%'
LIMIT 10;

确诊标志（需同时满足以下条件）：

1. 0 号算子为 TEMP TABLE TRANSFORMATION：

|0 |TEMP TABLE TRANSFORMATION|     |
|1 | TEMP TABLE INSERT       |TEMP1|
|2 |  TABLE FULL SCAN        |vw_xx| ← 被扫描表行数 >10 万
|3 | TEMP TABLE ACCESS       |VIEW1|
|4 | TEMP TABLE ACCESS       |VIEW2|

2. TEMP TABLE INSERT 下存在 TABLE FULL SCAN（而非 TABLE RANGE SCAN / TABLE GET），且被扫描表/视图行数大于 10 万行。
3. 拆开子查询单独执行能走索引：将关联谓词替换为具体常量值后，子查询计划变为 TABLE RANGE SCAN 或 TABLE GET。

-- 将 WHERE id = t.fk_id 中的 t.fk_id 替换为实际值验证
EXPLAIN SELECT col FROM vw WHERE id = <具体值>;
-- 预期：TABLE RANGE SCAN 或 TABLE GET

2. 运行时诊断：物化行数与输出行数对比

-- 查看实际执行各算子的 output_rows（正在执行中用 REQUEST_ID < 0）
SELECT plan_line_id, operator, name,
       output_rows, starts,
       last_change_time
FROM oceanbase.gv$ob_sql_plan_monitor
WHERE trace_id = '<target_trace_id>'
ORDER BY plan_line_id;

问题原因

本问题为优化器改写策略的代价评估不准（By Design，部分版本存在缺陷）。

改写机制

OceanBase 优化器将 SQL 中多次出现的相同子查询结构识别为公共子表达式，自动抽取为 CTE 物化执行（即 TEMP TABLE TRANSFORMATION 改写）。改写等价于将子查询改写为 WITH 子句：

原始: SELECT (子查询A WHERE c1=val1), (子查询A WHERE c1=val2) FROM t
改写: WITH TEMP1 AS (SELECT * FROM A)        ← 全量物化，不含任何过滤条件
     SELECT ... FROM TEMP1 WHERE c1=val1 ...  ← 从物化结果中过滤

执行计划固定三层结构： | 层 | 算子 | 作用 | | :--- | :--- | :--- | | 调度层 | TEMP TABLE TRANSFORMATION | 初始化/释放临时表，本身不执行计算 | | 写入层 | TEMP TABLE INSERT | 执行公共子计划，将结果物化到内存临时表 | | 读取层 | TEMP TABLE ACCESS | 各消费方从物化结果读取数据 |

性能退化根因

物化阶段（TEMP TABLE INSERT）执行的是不含任何外层谓词的完整子查询，原本可作为执行参数下推到子查询的关联谓词（WHERE col = outer.col）在物化阶段无法生效： | 维度 | 有 TEMP TABLE 抽取（物化） | 无抽取（正常路径） | | :--- | :--- | :--- | | 子查询执行方式 | 全表扫描 → 全量物化 → 扫描过滤 | 执行时传入关联值 → 索引精准定位 | | 索引利用 | 物化后关联谓词失效 | 可走主键/唯一索引 | | 扫描量级 | O(N)，N = 全表/视图行数 | O(1) 或 O(logN) | 五类触发场景及根因： | 场景 | 根因 | | :--- | :--- | | A. 标量子查询引用同一视图/表 + 关联谓词 | 合并物化后关联谓词无法下推，视图全量扫描 | | B. 引用 UNION ALL 大视图 | 物化须扫描所有分支基表，各分支条件均无法穿透 | | C. CASE WHEN 各分支嵌套子查询结构相似 | 各分支被合并为一个公共子计划，条件失去下推路径 | | D. OR 展开触发二次抽取 | OR Expansion 展为 UNION ALL 后，外层 NOT EXISTS 在各分支重复出现，再次触发抽取 | | E. 多 CTE 中存在无过滤的全量聚合 CTE | 无过滤 CTE 的全量物化拖大整体 TEMP TABLE 规模，其他 CTE 的过滤无法减少物化量 | 版本说明：OceanBase 4.2.5 对 CTE 抽取收益评估做了关键改进（新增静态谓词下压和 NLJ 动态谓词下压场景感知），4.2.5 之前版本更易触发上述误抽取，4.2.5 及之后版本大部分场景可自动规避。

问题的风险及影响

影响租户

该问题由查询优化器改写策略引起，MySQL 与 Oracle 模式租户均可能受影响。

影响版本

• OBServer 3.x：收益评估模型较弱，更易触发误抽取，所有场景均适用本文诊断方法。
• OBServer 4.x（低于 4.2.5）：仍可能触发，诊断方法相同。
• OBServer ≥ 4.2.5：新增静态谓词下压和 NLJ 动态谓词场景的感知，大部分场景可自动规避；如仍触发，可使用本文 Hint 方案止血。

解决方法

按影响范围从小到大三级止血

第一级：SQL Hint（推荐，仅影响当前 SQL，零风险）

-- 适用场景 A/B/E：禁用 TEMP TABLE 抽取（最常用）
SELECT /*+opt_param('xsolapi_generate_with_clause', 'false')*/
       <原始 SELECT 列表>
FROM <原始 FROM/WHERE>;
-- 适用场景 C（CASE WHEN 误抽取）：禁止所有查询改写
SELECT /*+ no_rewrite */ ...;
-- 适用场景 D（OR 展开触发二次抽取）：仅禁止 OR 展开
SELECT /*+ no_expand */ ...;

添加 Hint 后执行 EXPLAIN 确认计划不再出现 TEMP TABLE TRANSFORMATION，并对比实际执行耗时。 第二级：系统/租户级别（需评估全局影响，业务低峰执行）

-- 修改前，先确认该租户下是否有其他 SQL 依赖 TEMP TABLE 物化收益
ALTER SYSTEM SET "_xsolapi_generate_with_clause" = false;

执行前需评估：该租户是否存在多次引用同一无关联谓词子查询的重计算场景（此类 SQL 依赖物化收益，全局关闭会导致其性能下降）。

各场景对应止血方案速查

规避方式

用户侧规避

1. 改写高风险 SQL：避免两个子查询引用相同视图

-- 不推荐：两个标量子查询引用同一视图（易触发 TEMP TABLE 抽取）
SELECT
  (SELECT col FROM vw WHERE id = t.fk1),
  (SELECT col FROM vw WHERE id = t.fk2)
FROM t;
-- 推荐：改写为 LEFT JOIN，各自携带独立过滤条件
SELECT v1.col, v2.col
FROM t
LEFT JOIN vw v1 ON v1.id = t.fk1
LEFT JOIN vw v2 ON v2.id = t.fk2;

2. 拆分 CASE WHEN 多分支复杂子查询 CASE WHEN 各分支的嵌套子查询引用相同表集合但关联条件不同时，应在应用层拆为独立 SQL 分别执行，避免优化器将不同关联条件的子查询误合并：

-- 不推荐：两分支含相似嵌套子查询（ACCT_TRANSACTION 案例，代价 125 亿）
SELECT CASE
  WHEN (子查询含 serialno='A') IN ('0055','0040') THEN (子查询含 serialno='A')
  WHEN (子查询含 serialno='A') IN ('4020','4030') THEN (子查询含其他条件)
END FROM dual;
-- 推荐：应用层先判断 TRANSCODE，再分支执行对应查询

3. 避免 OR + NOT EXISTS 组合写法 WHERE 中多个 OR+IN(子查询) 与外层 NOT EXISTS 的组合是 D 类问题的固定触发模式，可改写为 UNION ALL 显式拆分或重构查询逻辑：

-- 不推荐
WHERE (col IN (子查询1) OR col IN (子查询2)) AND NOT EXISTS (排除子查询)
-- 推荐：显式 UNION ALL + 各分支独立 NOT EXISTS
SELECT ... WHERE col IN (子查询1) AND NOT EXISTS (排除子查询)
UNION ALL
SELECT ... WHERE col IN (子查询2) AND NOT EXISTS (排除子查询)

4. 对全量聚合 CTE 预物化为汇总表 变化频率低的全量聚合 CTE（如市场汇总、城市汇总），使用物化视图或汇总表定期预计算，查询时直接引用：

-- 离线预计算汇总结果
CREATE MATERIALIZED VIEW mv_box_summary REFRESH COMPLETE ON DEMAND AS
SELECT cinema_id, SUM(box_office) AS total FROM boxoffice GROUP BY cinema_id;
-- 查询时替代 CTE，避免物化大表
SELECT s.total FROM base_info b JOIN mv_box_summary s ON s.cinema_id = b.cinema_id
WHERE b.standard_id IN (...);

运维规避

1. 升级至 4.2.5 及以上版本 4.2.5 版本改进了 CTE 抽取的收益评估模型（静态谓词下压 + NLJ 动态谓词感知），升级后大部分场景可自动规避，无需修改 SQL。 2. 通过 Outline 绑定稳定计划 对已确认的问题 SQL，使用 Outline 绑定禁用 TEMP TABLE 改写的计划，防止优化器版本升级或统计信息变化导致计划反复：

CREATE OUTLINE fix_temp_table ON '<sql_id>'
USING HINT /*+opt_param('xsolapi_generate_with_clause', 'false')*/;

3. 新 SQL 上线前执行 EXPLAIN 验证 对含以下特征的 SQL，上线前执行 EXPLAIN EXTENDED 确认计划中无 TEMP TABLE TRANSFORMATION，或验证物化行数与输出行数之比合理（小于 100 倍）：

• 两个及以上引用相同视图/表的标量子查询
• CASE WHEN 多分支各含嵌套子查询
• OR + NOT EXISTS 组合
• 多 CTE 且其中含无 WHERE 条件的聚合 CTE