---
title: TEMP TABLE TRANSFORMATION 改写导致查询性能劣化-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于TEMP TABLE TRANSFORMATION 改写导致查询性能劣化相关的常见问题和使用技巧，帮助您快速解决TEMP TABLE TRANSFORMATION 改写导致查询性能劣化的难题。
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

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

更新时间：2026-08-25 06:56

适用版本： V3.1.x、V3.2.x、V3.3.x、V4.0.x、V4.1.x、V4.2.x 内容类型：Troubleshoot  

## 问题现象

| Skill 名称 | `sql-wiki-temp-table-transformation-diagnosis` |
| --- | --- |
| 适用范围 | 诊断 OceanBase TEMP TABLE TRANSFORMATION 改写导致查询性能劣化的问题，覆盖相关谓词下推失效、视图全表扫描、CASE WHEN 误抽取、OR 展开触发二次抽取、过度物化五类场景 |

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

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

```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：

```sql
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 抽取是否已被全局关闭（若已关闭，则问题另有原因）：

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

```

### 事后诊断

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

```sql
-- 获取执行计划
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**：

```sql
|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`。

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

```

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

```sql
-- 查看实际执行各算子的 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;

```

| 判断标准 | 含义 |
| --- | --- |
| `TEMP TABLE INSERT.output_rows` / 主查询输出行数 > **10000** | 确认过度物化 |
| `TEMP TABLE ACCESS.REAL.TIME` / `TEMP TABLE ACCESS.EST.TIME` > **1000 倍** | 物化后全量扫描，估算严重失准 |
| `TEMP TABLE INSERT.last_change_time` 长时间无更新 | 物化阶段卡住（大表扫描中） |

## 问题原因

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

### 改写机制

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

```text
原始: 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 及之后版本大部分场景可自动规避。

## 问题的风险及影响

| 影响维度 | 说明 |
| --- | --- |
| RT 急剧劣化 | 索引点查（O(1)）退化为全表扫描（O(N)），耗时从毫秒升至秒/十秒级，严重影响前台业务可用性 |
| 内存压力飙升 | TEMP TABLE 物化在内存中缓存全量数据，大表场景（百万行以上）可能挤占 SQL Work Area，迫使 Hash Join 等其他算子落盘，进一步拖慢整体执行 |
| CPU 持续高负载 | 大表全量扫描 + HASH JOIN/HASH DISTINCT 等重计算算子并发执行，CPU 居高不下 |
| 估算失准，问题难发现 | 优化器 `EST.TIME` 基于物化方案估算，与实际执行差异悬殊（可达 10 万倍），无法通过 EXPLAIN 代价值预判问题 |
| 全局禁用的副作用 | 若通过系统参数全局关闭 TEMP TABLE 抽取，可能影响依赖物化去重复计算收益的其他 SQL（如多次引用相同大型无关联子查询的场景） |

## 影响租户

| sys | MySQL | Oracle |
| --- | --- | --- |
| NO | YES | YES |

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

## 影响版本

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

## 解决方法

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

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

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

```

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

### 各场景对应止血方案速查

| 场景 | 识别方式 | 止血 Hint |
| --- | --- | --- |
| A. 标量子查询引用同一视图 + 关联谓词 | 两个子查询引用相同表/视图；TEMP TABLE INSERT 下 TABLE FULL SCAN | `opt_param('xsolapi_generate_with_clause', 'false')` |
| B. UNION ALL 大视图全表扫描 | TEMP TABLE INSERT 下 UNION ALL + TABLE FULL SCAN | 同上 |
| C. CASE WHEN 各分支误抽取 | CASE WHEN SQL；各分支关联条件不同但引用相同表集合 | `no_rewrite` 或拆分 CASE WHEN |
| D. OR 展开 + NOT EXISTS 二次抽取 | WHERE 含多 OR+IN(子查询) + 外层 NOT EXISTS | `no_expand` |
| E. 多 CTE 过度物化 | 物化行数/输出行数 >10000；无过滤全量聚合 CTE | 拆分无过滤 CTE 为独立 SQL；`opt_param('xsolapi_generate_with_clause', 'false')` |

## 规避方式

### 用户侧规避

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

```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 分别执行，避免优化器将不同关联条件的子查询误合并：

```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 显式拆分或重构查询逻辑：

```sql
-- 不推荐
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（如市场汇总、城市汇总），使用物化视图或汇总表定期预计算，查询时直接引用：

```sql
-- 离线预计算汇总结果
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 改写的计划，防止优化器版本升级或统计信息变化导致计划反复：

```sql
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

Previous

[OceanBase 数据库内 SQL 执行出现 size overflow，日志出现 thread count reach limit 的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002397695)

Next

[如何为 CTE 公共表达式正确地添加 Hint](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000006417232) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线