--- title: CTE 计划下 SEMI JOIN 改写为 INNER JOIN 失败导致计划走偏的原因及解决方法-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 CTE 计划下 SEMI JOIN 改写为 INNER JOIN 失败导致计划走偏的原因及解决方法相关的常见问题和使用技巧，帮助您快速解决 CTE 计划下 SEMI JOIN 改写为 INNER JOIN 失败导致计划走偏的原因及解决方法的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 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 示例。 ```shell 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 计划后。 ```shell 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 版本。 ## 解决方法 1. 关闭参数 CTE 转换（OceanBase 数据库 V3.x BP8 之后版本支持）。 ```shell ALTER SYSTEM SET _xsolapi_generate_with_clause=false tenant='xxx'; ``` 2. 针对该 SQL 外围包层：`select /*+ no_rewrite*/ * from ()`。 3. 添加空函数，避免 CTE 转换。 ```shell 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 ``` Previous [自适应连接功能导致 SQL 执行报错 -4016](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005908626) Next [transfer 导致查询性能下降](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000899815) ![有帮助](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) 咨询热线