UNION ALL 这种 SET 类的子查询不支持 UNNEST 变成 JOIN

问题现象

在 OceanBase 数据库执行特定 SQL 时，执行效率极低，甚至几个小时没有结果。该 SQL 在 Oracle 数据库中执行速度较快。

问题 SQL 示例如下：

SELECT ... FROM 表 a,表 b 
WHERE a.usercode = '108013479'
AND a.yearmonth = '202501'
AND b.channelcode <> '18'
AND b.channelcode in
    (
        CONNECT BY 层次查询
        UNION ALL
        非 CONNECT BY 层次查询
    )

这里的表 b 本身是个非相关子查询。在这个问题里不重要。

关键诊断信息

触发条件

1. SQL 语句中使用了 IN 操作符，且其后的子查询包含了 UNION ALL 和层次查询。

2. UNION ALL 子查询中的每个部分都与父查询表 A 进行了等值判断，形成相关子查询。

3. UNION ALL 子查询中的层次查询性能不佳，且无法通过 UNNEST 转换为连接（JOINS）来提升查询性能。

事前巡检

1. 检查 SQL 语句是否存在 IN 操作符后跟 UNION ALL 和 CONNECT BY 层次查询的结构。

2. 检查原始语句里把 UNION ALL 去掉，原始语句里用 UNION ALL 的每个分支去分别测试，语句是否都是快的。

事后诊断

1. 对比 OceanBase 里使用 UNION ALL 和不使用 UNION ALL 的计划，确认使用 UNION ALL 子查询的原始语句是否使用了 SUBPLAN FILTER 而非 JOIN 方式，并且原始语句里去掉 UNION ALL 改成使用 UNION ALL 的单独每个分支的计划是否使用了 JOIN 方式。

2. 对比 Oracle 和 OceanBase 的执行计划，确认两者在处理相同 SQL 时是否存在显著差异。

问题原因

正常情况下，IN + UNION ALL 结构的 SQL 在 OceanBase 中也能有效执行。但在本案例中，由于 UNION ALL 的一个分支包含了层次查询，而 OceanBase 对层次查询的处理性能较差，且无法将此类子查询 UNNEST 转换为连接（JOINS），导致子查询被反复执行，严重影响了整体查询性能。

问题的风险及影响

SQL 执行非常慢。

影响租户

影响 OceanBase 数据库中的 Oracle 租户，对于 SYS 租户和 MySQL 租户无影响。

影响版本

OceanBase 数据库 V4.1.0 GA（oceanbase-4.1.0.0-100001122023040322）及之后版本、V4.2.0 GA（oceanbase-4.2.0.0-100010082023083014）及之后版本、V4.2.1 GA（oceanbase-4.2.1.0-100000182023092722）及之后版本、V4.2.2 GA（oceanbase-4.2.2.0-100000082024011317）及之后版本、V4.2.5 GA（oceanbase-4.2.5.0-100000082024102022）及之后版本、V4.3.0（oceanbase-4.3.0.0-100000072024020200）及之后版本、V4.3.5 GA（oceanbase-4.3.5.0-100000122024123020）及之后版本、V4.4.0（oceanbase-4.4.0.0-100000562025070723）及之后版本、V4.5.0 GA（oceanbase-4.5.0.0-100000172025112420）及之后版本。

解决方法

1. 将 IN (层次查询 UNION ALL 非层次查询) 结构的 SQL 改写为 IN 层次查询 OR IN 非层次查询 的形式。

2. 手动改写语句之后，业务的计划还是没有 UNNEST，虽然在本地空表测试 IN OR IN 写法是已经可以 UNNEST 了。这时候使用 /*+ USE_CONCAT */ Hit 提示来帮助优化器选择 UNNEST 展开子查询变成 HASH JOIN。

规避方式

改写语句 IN (... UNION ALL ...) 的写法为 IN ... OR IN ... 写法，如果还是没有 UNNEST，那么再使用上 /*+ USE_CONCAT */ Hint 提示。