划线反馈

CTE 计划下 SEMI JOIN 改写为 INNER JOIN 失败导致计划走偏的原因及解决方法

更新时间：2026-05-14 09:21

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

问题现象

SQL 中有冗余部分时候，优化器会主动提取到公共表达式并通过临时表来物化的方式优化，但有些场景当计划中进行 CTE 物化后会导致一些 SEMI JOIN SQL 改写为 INNER JOIN 规则失效。导致计划选择错误驱动表。

问题原因

SEMI to INNER 改写可以将 SEMI JOIN 改写为 INNER JOIN，使得优化器可以生成将 SEMI JOIN 右表作为驱动表的连接路径。优化器通常会主动进行改写来优化，如下 SQL 示例。

select     b.WARRANT_ID as warrantId
    from xxxxx.xxxxx_xxxxx_COPY b
    where b.MORTAGAGE_ID in (select MORTAGAGE_ID from (
    select   a.MORTAGAGE_ID from egifuser.MORTAGAGE_COPY a
    where a.COLLATERAL_ID=8558  
    union all 
    select   b.MORTAGAGE_ID from egifuser.MORTAGAGE b
    where b.COLLATERAL_ID=8558   
    ))
    union all
    select    c.WARRANT_ID as warrantId
    from xxxxx.xxxxx_xxxxx c
    left join xxxxx.xxxxx_xxxxx_COPY d
    on c.REL_ID=d.REL_ID
    where c.MORTAGAGE_ID in (select MORTAGAGE_ID from (
    select   a.MORTAGAGE_ID from egifuser.MORTAGAGE_COPY a
    where a.COLLATERAL_ID=8558 
    union all 
    select    b.MORTAGAGE_ID from egifuser.MORTAGAGE b
    where b.COLLATERAL_ID=8558
    ))
    and d.REL_ID is null

*************************** 1. row ***************************
Query Plan: =================================================================
|ID|OPERATOR                 |NAME            |EST. ROWS|COST   |
-----------------------------------------------------------------
|0 |TEMP TABLE TRANSFORMATION|                |7        |3470966|
|1 | TEMP TABLE INSERT       |TEMP1           |2        |1      |
|2 |  UNION ALL              |                |2        |1      |
|3 |   TABLE SCAN            |a(MORT_COLID2)  |1        |1      |
|4 |   TABLE SCAN            |b(MORT_COLID)   |1        |1      |
|5 | UNION ALL               |                |7        |3470965|
|6 |  MERGE SEMI JOIN        |                |4        |9232   |
|7 |   TABLE SCAN            |b(warrant_rel_1)|22970    |8885   |
|8 |   SORT                  |                |2        |1      |
|9 |    TEMP TABLE ACCESS    |VIEW1(TEMP1)    |2        |1      |
|10|  NESTED-LOOP OUTER JOIN |                |3        |3461733|
|11|   MERGE SEMI JOIN       |                |3        |3461647|
|12|    TABLE SCAN           |c(warrant_rel_1)|8614605  |3332170|
|13|    SORT                 |                |2        |1      |
|14|     TEMP TABLE ACCESS   |VIEW2(TEMP1)    |2        |1      |
|15|   TABLE GET             |d               |1        |29     |
=================================================================

Outputs & filters: 
-------------------------------------
  0 - output([UNION([1])]), filter(nil)
  1 - output([1]), filter(nil)
  2 - output([UNION([1])]), filter(nil)
  3 - output([a.MORTAGAGE_ID]), filter(nil), 
      access([a.MORTAGAGE_ID]), partitions(p0)
  4 - output([b.MORTAGAGE_ID]), filter(nil), 
      access([b.MORTAGAGE_ID]), partitions(p0)
  5 - output([UNION([1])]), filter(nil)
  6 - output([b.WARRANT_ID]), filter(nil), 
      equal_conds([b.MORTAGAGE_ID = VIEW1.MORTAGAGE_ID]), other_conds(nil)
  7 - output([b.MORTAGAGE_ID], [b.WARRANT_ID]), filter(nil), 
      access([b.MORTAGAGE_ID], [b.WARRANT_ID]), partitions(p0)
  8 - output([VIEW1.MORTAGAGE_ID]), filter(nil), sort_keys([VIEW1.MORTAGAGE_ID, ASC])
  9 - output([VIEW1.MORTAGAGE_ID]), filter(nil), 
      access([VIEW1.MORTAGAGE_ID])
  10 - output([c.WARRANT_ID]), filter([(T_OP_IS, d.REL_ID, NULL, 0)]), 
      conds(nil), nl_params_([c.REL_ID])
  11 - output([c.WARRANT_ID], [c.REL_ID]), filter(nil), 
      equal_conds([c.MORTAGAGE_ID = VIEW2.MORTAGAGE_ID]), other_conds(nil)
  12 - output([c.REL_ID], [c.MORTAGAGE_ID], [c.WARRANT_ID]), filter(nil), 
      access([c.REL_ID], [c.MORTAGAGE_ID], [c.WARRANT_ID]), partitions(p0)
  13 - output([VIEW2.MORTAGAGE_ID]), filter(nil), sort_keys([VIEW2.MORTAGAGE_ID, ASC])
  14 - output([VIEW2.MORTAGAGE_ID]), filter(nil), 
      access([VIEW2.MORTAGAGE_ID])
  15 - output([d.REL_ID]), filter(nil), 
      access([d.REL_ID]), partitions(p0)

1 row in set (0.16 sec)

上述 SQL 在 C 表做驱动表时产生耗时较高，从计划上看分析原因是 JOIN 驱动表 SEMI JOIN 改写为 INNER JOIN 失效导致，失效原因和 CTE 公共表达式物化相关，导致 SEMI JOIN 无法被改写成 INNER JOIN，去选择正确驱动表。

关闭 CTE 计划后。

MySQL [(none)]> explain select     b.WARRANT_ID as warrantId from xxxxx.xxxxx_xxxxx_COPY b where b.MORTAGAGE_ID in (select MORTAGAGE_ID from ( select   a.MORTAGAGE_ID from egifuser.MORTAGAGE_COPY a where a.COLLATERAL_ID=8558   union all  select   b.MORTAGAGE_ID from egifuser.MORTAGAGE b where b.COLLATERAL_ID=8558    )) union all select    c.WARRANT_ID as warrantId from xxxxx.xxxxx_xxxxx c left join xxxxx.xxxxx_xxxxx_COPY d on c.REL_ID=d.REL_ID where c.MORTAGAGE_ID in (select MORTAGAGE_ID from ( select   a.MORTAGAGE_ID from egifuser.MORTAGAGE_COPY a where a.COLLATERAL_ID=8558  union all  select    b.MORTAGAGE_ID from egifuser.MORTAGAGE b where b.COLLATERAL_ID=8558 )) and d.REL_ID is null\G
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR               |NAME            |EST. ROWS|COST|
------------------------------------------------------------
|0 |UNION ALL              |                |7        |100 |
|1 | NESTED-LOOP JOIN      |                |4        |7   |
|2 |  SUBPLAN SCAN         |VIEW3           |2        |3   |
|3 |   HASH UNION DISTINCT |                |2        |3   |
|4 |    TABLE SCAN         |a(MORT_COLID2)  |1        |1   |
|5 |    TABLE SCAN         |b(MORT_COLID)   |1        |1   |
|6 |  TABLE SCAN           |b(warrant_rel_1)|2        |1   |
|7 | NESTED-LOOP OUTER JOIN|                |3        |93  |
|8 |  NESTED-LOOP JOIN     |                |3        |7   |
|9 |   SUBPLAN SCAN        |VIEW4           |2        |3   |
|10|    HASH UNION DISTINCT|                |2        |3   |
|11|     TABLE SCAN        |a(MORT_COLID2)  |1        |1   |
|12|     TABLE SCAN        |b(MORT_COLID)   |1        |1   |
|13|   TABLE SCAN          |c(warrant_rel_1)|2        |1   |
|14|  TABLE GET            |d               |1        |29  |
============================================================

Outputs & filters: 
-------------------------------------
  0 - output([UNION([1])]), filter(nil)
  1 - output([b.WARRANT_ID]), filter(nil), 
      conds(nil), nl_params_([VIEW3.VIEW1.MORTAGAGE_ID])
  2 - output([VIEW3.VIEW1.MORTAGAGE_ID]), filter(nil), 
      access([VIEW3.VIEW1.MORTAGAGE_ID])
  3 - output([UNION([1])]), filter(nil)
  4 - output([a.MORTAGAGE_ID]), filter(nil), 
      access([a.MORTAGAGE_ID]), partitions(p0)
  5 - output([b.MORTAGAGE_ID]), filter(nil), 
      access([b.MORTAGAGE_ID]), partitions(p0)
  6 - output([b.WARRANT_ID]), filter(nil), 
      access([b.WARRANT_ID]), partitions(p0)
  7 - output([c.WARRANT_ID]), filter([(T_OP_IS, d.REL_ID, NULL, 0)]), 
      conds(nil), nl_params_([c.REL_ID])
  8 - output([c.WARRANT_ID], [c.REL_ID]), filter(nil), 
      conds(nil), nl_params_([VIEW4.VIEW2.MORTAGAGE_ID])
  9 - output([VIEW4.VIEW2.MORTAGAGE_ID]), filter(nil), 
      access([VIEW4.VIEW2.MORTAGAGE_ID])
  10 - output([UNION([1])]), filter(nil)
  11 - output([a.MORTAGAGE_ID]), filter(nil), 
      access([a.MORTAGAGE_ID]), partitions(p0)
  12 - output([b.MORTAGAGE_ID]), filter(nil), 
      access([b.MORTAGAGE_ID]), partitions(p0)
  13 - output([c.REL_ID], [c.WARRANT_ID]), filter(nil), 
      access([c.REL_ID], [c.WARRANT_ID]), partitions(p0)
  14 - output([d.REL_ID]), filter(nil), 
      access([d.REL_ID]), partitions(p0)

1 row in set (0.15 sec)

问题的风险及影响

SQL 性能不优，SQL 超时。

影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

适用版本

OceanBase 数据库 V3.x 版本。

解决方法

关闭参数 CTE 转换（OceanBase 数据库 V3.x BP8 之后版本支持）。
```
ALTER SYSTEM SET _xsolapi_generate_with_clause=false tenant='xxx';
```
针对该 SQL 外围包层：select /*+ no_rewrite*/ * from ()。

添加空函数，避免 CTE 转换。

delimiter //
create function f1() returns int not deterministic return 1;  
//
select     b.WARRANT_ID as warrantId
 from xxxxx.xxxxx_xxxxx_COPY b
 where b.MORTAGAGE_ID in (select distinct MORTAGAGE_ID from (
 select   a.MORTAGAGE_ID from egifuser.MORTAGAGE_COPY a
 where a.COLLATERAL_ID=8558  and f1() 
 union all 
 select   b.MORTAGAGE_ID from egifuser.MORTAGAGE b
 where b.COLLATERAL_ID=8558    and f1() 
 ))
 union all
 select    c.WARRANT_ID as warrantId
 from xxxxx.xxxxx_xxxxx c
 left join xxxxx.xxxxx_xxxxx_COPY d
 on c.REL_ID=d.REL_ID
 where c.MORTAGAGE_ID in (select distinct MORTAGAGE_ID from (
 select   a.MORTAGAGE_ID from egifuser.MORTAGAGE_COPY a
 where a.COLLATERAL_ID=8558 
 union all 
 select    b.MORTAGAGE_ID from egifuser.MORTAGAGE b
 where b.COLLATERAL_ID=8558
 ))
 and d.REL_ID is null

自适应连接功能导致 SQL 执行报错 -4016

transfer 导致查询性能下降

OceanBase 数据库