当前位置： 首页 > 优化 > 正文

从一次SQLMAP报错说起:YashanDB上Query到CTE改写的变相落地法

huangtingzhong 发布于 2026-08-15 15:12优化抢沙发

写在前面

最近在一个项目中优化SQL语句时，本以为SQL优化so easy的，但是啪啪打脸，遇到了大麻烦。在SQL优化中，常常遇到下来这类很典型的诉求：

  • 线上语句是普通 Query（SELECT 起头，内含多段重复子查询 / 内联视图）；
  • 优化方向明确：改写成 CTE，让公共中间结果物化一次、多处引用；
  • 应用侧不改代码，在YashanDB中用 SQLMAP 把「旧 Query」映射到「新 CTE」，对业务透明。

理想闭环是：

慢 Query  →  CTE 改写（验证计划与结果）  →  CREATE SQLMAP  →  应用仍发旧 SQL，跑新计划

真正建映射时却踩了坑：源是 Query，目标是 leading WITH … 的 CTE，CREATE SQLMAP 直接报错。

本文就以这次报错为起点，来探寻此问题怎么解决；再用一套实验室模拟案例（非该项目原 SQL）把完整思路、变通写法和前后计划 / 性能对比演示清楚，便于同类场景复用。

一、项目现场：Query 改 CTE，SQLMAP 报错

优化方案本身没有争议——公共过滤与连接抽成 CTE，减少重复扫描。卡点出在「固化」这一步：把已经验证过的 CTE 文本登记为 SQLMAP 目标时，引擎返回：

YAS-04810 not the same sql type

对应操作形态是：

CREATE SQLMAP <name>
  (ALL,
   '<应用仍在发送的 SELECT … 源文本>',
   '<以 WITH … AS (…) SELECT … 开头的目标文本>');

1.1 报错在说什么

YashanDB 的 SQLMAP 要求源、目标属于同一 SQL 类型。
以 SELECT 起头的语句，与以 WITH 起头的语句，在类型判定上不一致，官方创建路径会直接拒绝——与文本长短、是否语义等价无关。

这对 DBA 的实际含义是：

你可以在会话里把 CTE 跑通、计划也漂亮；但只要应用发出的仍是 Query，而映射目标写成 leading WITH，SQLMAP 这一层过不去。

1.2 现场还试过什么（结论先行）

做法 结果
SELECT … → leading WITH … 失败 YAS-04810
WITH … → WITH … 可创建，但解决不了「应用仍发 SELECT」
短 stub CREATE 后再 UPDATE SYS.SQL_MAP$，把目标改成 leading WITH 基表能写上，reload 后执行源 SQL 可能 YAS-04110，不可作为正式手段

因此：优化目标仍是「CTE 物化」；工程约束变成——映射目标文本必须以与源相同的 SQL 类型呈现（对外仍是 SELECT 外壳）。

下面用模拟案例把「如何既保住 CTE 物化，又让 SQLMAP 合法」走通一遍。模拟数据与业务表均非原项目对象，只借同一类结构说明方法。

二、模拟案例：用可控场景演示整条思路

2.1 模拟设定

在 lab（YashanDB 23.5.2.101）构造四表场景，刻意弱化二级索引，放大「重复子查询」的逻辑读：

对象 规模 角色
ytop_case_dept 4 部门维表
ytop_case_grade 5 薪等
ytop_case_emp 80,000 员工
ytop_case_bonus 200,000 多年度奖金

过滤（改写前后一致）：grade >= 3、job <> 'CLERK'、bonus.yyyy = 2025。

运维与观测统一走 ytop（本机有 yasql 时直接 -f；远端 lab 用机上 ytop_linux_arm64 -f，或本机 ytop -t <host> -f）：

步骤 命令
建映射 ytop -f sqlmap_create_by_sqlid.sql（提示输入 source / target 两个 sql_id）
看计划 ytop -f plan_by_sqlid.sql
查 / 删 map ytop -f sqlmap.sql / ytop -f sqlmap_drop.sql
重载 matcher ALTER SYSTEM SET sql_map = TRUE（create 脚本不自动执行，需手工）

补充对照（可选）：STATISTICS_LEVEL=ALL + AUTOTRACE + TIMING，关注 Elapsed、A-Time / A-Rows、db block gets。

2.2 模拟中的「慢 Query」长什么样

与项目中同类：同一套 emp、grade 过滤写了两遍——一支统计高薪人数，一支再关联奖金：

SELECT /*YTPERF_SRC_V1*/ COUNT(*) AS dept_cnt,
       NVL(SUM(high_sal_cnt),0) AS sum_high,
       NVL(SUM(bonus_emp_cnt),0) AS sum_bonus
  FROM (
    SELECT d.deptno, d.dname, s.high_sal_cnt, b.bonus_emp_cnt
      FROM ytop_case_dept d,
           (SELECT e.deptno, COUNT(*) AS high_sal_cnt
              FROM ytop_case_emp e, ytop_case_grade g
             WHERE e.sal BETWEEN g.losal AND g.hisal
               AND g.grade >= 3 AND e.job <> 'CLERK'
             GROUP BY e.deptno) s,
           (SELECT e.deptno, COUNT(DISTINCT e.empno) AS bonus_emp_cnt
              FROM ytop_case_emp e, ytop_case_grade g, ytop_case_bonus b
             WHERE e.sal BETWEEN g.losal AND g.hisal
               AND g.grade >= 3 AND e.job <> 'CLERK'
               AND b.empno = e.empno AND b.yyyy = 2025
             GROUP BY e.deptno) b
     WHERE d.deptno = s.deptno AND d.deptno = b.deptno
  );

语义正确，计划上却是两段独立子树，优化器很难自动「算一次、用两次」。

2.3 改写前计划与统计（模拟实测）

结果：dept_cnt=4, sum_high=40512, sum_bonus=40512
Elapsed: 00:00:00.142｜根节点 A-Time ≈ 137493

| Id | Operation                         | Name            | A - Rows | A - Time | Loops   |
|  0 | SELECT STATEMENT                  |                 |        1 |   137493 |       2 |
|  1 |  AGGREGATE                        |                 |        1 |   137493 |       2 |
| ...|
|* 7 |        MERGE JOIN INNER           |                 |    40512 |   113973 |   40513 |
|  9 |          NESTED LOOPS INNER       |                 |    53334 |    83495 |   53335 |
|*10 |           TABLE ACCESS FULL       | YTOP_CASE_BONUS |    80000 |     9046 |   80001 |
|*12 |            INDEX UNIQUE SCAN      | (EMP PK)        |    53334 |    64988 |  133334 |
| ...|
|*25 |           TABLE ACCESS FULL       | YTOP_CASE_EMP   |    53334 |     4452 |   53335 |

          0 physical reads
     240889 db block gets
Elapsed: 00:00:00.142

读计划时的结论（与项目诊断同构）：

  1. 无 LOAD AS TEMP TABLE，公共中间结果未物化复用；
  2. 奖金支 NL + 唯一索引 Loops 到 10 万+，时间堆在深层连接；
  3. emp FULL 在多支路径重复出现；
  4. 物理读为 0（热缓存）时，db block gets = 240889 仍能说明重复劳动有多重。

三、优化思路：先 CTE，再谈 SQLMAP

3.1 改写目标

base_emp  (emp ⋈ grade，过滤一次)
    ├─ high_sal   COUNT GROUP BY deptno
    └─ bonus_emp  ⋈ bonus(2025) 后再 COUNT
         └─ 与 dept 拼接

说明：同一 CTE 被多处引用时，优化器可能将其物化为临时表；是否生效以执行计划是否出现 LOAD AS TEMP TABLE / TEMP TABLE ACCESS 为准，不要想当然。

3.2 和现场报错如何对齐

若把上面 CTE 写成 leading WITH，再对「应用 Query」做 SQLMAP，就会再次得到 YAS-04810——这正是项目里卡住的那一步。

矛盾可以概括成：

  • 优化侧希望目标是「多引用 CTE」，最好 leading WITH 一眼能看出物化意图；
  • SQLMAP 侧要求目标与源同型，源是 Query 时目标就不能以 WITH 起头。

因此模拟里要演示的不是「再撞一次墙」，而是把 CTE 嵌进 SELECT 外壳：类型合法交给外壳，物化语义留给内层 WITH（详见下一节）。

四、变相落地：内嵌 CTE，让 SQLMAP 合法

4.0 为什么要「内嵌」而不是 leading WITH（设计重点）

现场优化要的是 CTE 物化，不是「把 SQL 改成任何一种写法都行」。理想文本往往是：

WITH base_emp AS (...),   -- 算一次
     high_sal AS (...引用 base_emp...),
     bonus_emp AS (...再引用 base_emp...)
SELECT ...

但应用发出的仍是 SELECT … 起头的 Query。若 SQLMAP 目标也写成上列 leading WITH，创建阶段就会 YAS-04810 not the same sql type——类型检查在语义等价之前就挡掉了。

因此目标文本必须同时满足两件事：

约束 要求 若不满足
SQLMAP 类型 目标对外仍是 Query（与源同型） YAS-04810，map 建不成
优化语义 公共中间结果在 同一 WITH 作用域内被多处引用 计划仍可能双支重复扫，逻辑读下不来

内嵌 CTE 正是同时满足这两点的折中形态：

SELECT …                    ← 外壳：对 SQLMAP 仍是 Query，与源同型
  FROM (
    WITH base_emp AS (...), ← 内核：完整 CTE 图，多引用 → 可 TEMP 物化
         high_sal AS (...),
         bonus_emp AS (...)
    SELECT …
  );

设计上刻意拆成「外壳 + 内核」：

  1. 外壳只服务登记：最外层 SELECT … FROM ( … ) 不承担优化，只把语句类型留在 Query 一侧，让 CREATE SQLMAP / sqlmap_create_by_sqlid.sql 能过类型检查。
  2. 内核才是优化本体：括号内仍是完整 WITH 多 CTE；base_emp 被 high_sal、bonus_emp 各引用一次，优化器才可能生成 LOAD AS TEMP TABLE / TEMP TABLE ACCESS——这才是逻辑读骤降的来源。
  3. 物化验收看计划，不看关键字位置：WITH 写在括号里还是语句最前，对「能否物化」不是充分条件；必须以计划里是否出现 TEMP 为准。内嵌写法是在 不牺牲物化能力 的前提下，换取 SQLMAP 合法。
  4. 拒绝伪方案：把 leading WITH 硬 UPDATE 进 SQL_MAP$ 可能绕过创建检查，但执行时常踩 YAS-04110 等坑；改应用直接发 WITH 则失去「应用无感」前提。内嵌 CTE 是正式路径。

一句话：内嵌不是为了「看起来像 CTE」，而是为了在「源必须是 Query」的 SQLMAP 约束下，把 CTE 物化语义合法地塞进目标文本。

4.1 目标文本形态（模拟）

SELECT /*YTPERF_TGT_V1*/ COUNT(*) AS dept_cnt,
       NVL(SUM(high_sal_cnt),0) AS sum_high,
       NVL(SUM(bonus_emp_cnt),0) AS sum_bonus
  FROM (
    WITH base_emp AS (
      SELECT e.empno, e.deptno, e.sal, e.job, g.grade
        FROM ytop_case_emp e, ytop_case_grade g
       WHERE e.sal BETWEEN g.losal AND g.hisal
         AND g.grade >= 3 AND e.job <> 'CLERK'
    ),
    high_sal AS (
      SELECT deptno, COUNT(*) AS high_sal_cnt FROM base_emp GROUP BY deptno
    ),
    bonus_emp AS (
      SELECT be.deptno, COUNT(DISTINCT be.empno) AS bonus_emp_cnt
        FROM base_emp be, ytop_case_bonus b
       WHERE b.empno = be.empno AND b.yyyy = 2025
       GROUP BY be.deptno
    )
    SELECT d.deptno, d.dname, s.high_sal_cnt, b.bonus_emp_cnt
      FROM ytop_case_dept d, high_sal s, bonus_emp b
     WHERE d.deptno = s.deptno AND d.deptno = b.deptno
  );

对照 4.0 读这段文本：

  1. 最外层 SELECT … FROM ( … ) → 与源同为 Query 类型，CREATE SQLMAP 可通过；
  2. 括号内完整 WITH 多 CTE，base_emp 被引用两次 → 仍可触发 TEMP 物化；
  3. 先单独执行目标，确认计划已有 LOAD AS TEMP TABLE，再登记 SQLMAP；
  4. 不要用 UPDATE SQL_MAP$ 硬塞 leading WITH 碰运气。

4.2 创建映射（用 ytop 内置脚本）

先分别执行源 / 目标 SQL，让文本进入 gv$sql，再：

# 交互：依次输入 source_sqlid、target_sqlid
ytop -f sqlmap_create_by_sqlid.sql

# create 成功后手工重载（脚本只打印提示，不自动 ALTER SYSTEM）
# ALTER SYSTEM SET sql_map = TRUE;

# 仍执行「源 SQL 文本」回归；计划用内置脚本看
ytop -f plan_by_sqlid.sql          # 输入源 sql_id
ytop -f sqlmap.sql                 # 按 map 名或 sql_id 查看

等价于手工 CREATE SQLMAP … (ALL, '<源全文>', '<目标全文>')，但由脚本从 gv$sql 按 sql_id 取全文并处理长度 / stub 回退。
实验室一键复现：smoke/sqlmap/run_case_perf_ytop.sh（已验证：SQLMAP created … (via DBMS_SQL)，映射后 plan_by_sqlid 出现 LOAD AS TEMP TABLE BASE_EMP，gv$sql.buffer_gets 数量级从约 24 万降至约千级）。

五、映射后：计划与性能

仍提交源 SQL 文本。结果集不变；计划切到与目标同构的 CTE 物化形态。

5.1 映射后计划（摘录）

Elapsed: 00:00:00.094｜根节点 A-Time ≈ 90560

| Id | Operation                      | Name            | A - Rows | A - Time | Loops  |
|  0 | SELECT STATEMENT               |                 |        1 |    90560 |      2 |
|  1 |  LOAD AS TEMP TABLE            | BASE_EMP        |          |          |        |
|* 2 |   MERGE JOIN INNER            |                 |    40512 |    38054 |  40513 |
|* 7 |     TABLE ACCESS FULL         | YTOP_CASE_EMP   |    53334 |     5549 |  53335 |
|  8 |  AGGREGATE                    |                 |        1 |    90559 |      2 |
| 15 |         TEMP TABLE ACCESS     | BASE_EMP        |    40512 |     1359 |  40513 |
|*16 |         TABLE ACCESS FULL     | YTOP_CASE_BONUS |    80000 |     7249 |  80001 |
| 19 |        TEMP TABLE ACCESS      | BASE_EMP        |    40512 |    45840 |  40513 |

          0 physical reads
        885 db block gets
Elapsed: 00:00:00.094

5.2 前后对比

指标 SQLMAP 前（源文本） SQLMAP 后（同一源文本） 变化
结果 4 / 40512 / 40512 4 / 40512 / 40512 一致
Elapsed 0.142 s 0.094 s 约 −34%
A-Time 137493 90560 约 −34%
physical reads 0
赏

您可能还会对这些文章感兴趣！

从一次SQLMAP报错说起:YashanDB上Query到CTE改写的变相落地法：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter