包含 NLJ 的 SQL 由于优化器默认选择条件下压计划而选择了错误的索引，生成不优计划

问题现象

SQL 执行不优，选择了较差/代价高的索引，导致计划不优，执行性能差。

如下 SQL 语句：

Select 'Y'
FROM lifedata.POL_INFO A,
    lifedata.PREM_INFO B,
    lifedata.UNDW_CONTROL C
WHERE A.POLNO = 'XXXX'
    AND A.POLNO = B.POLNO
    AND A.COPY_ORDINAL > 0
    AND C.CONTROL_ID = 'YGZ_INVOICE_TIMESET'
    AND B.PAYMENT_DATE < C.CONTROL_DATE
    AND B.AMT_TYPE IN ('21', 'A1', '2E');

默认生成了如下图一所示的计划：

由上图一可知优化器默认生成了 B 表使用索引 IX_PREM_INFO_PAYD_AMT_PAY 可以走上关联条件（B.PAYMENT_DATE < C.CONTROL_DATE） 下压（2 号算子 nl_params_([C.CONTROL_DATE(:1)])）的计划。但是从图一计划中，可以看到该计划的代价非常高，B表就需要 199973997us。

如对 B 表绑定索引（IX_PREM_INFO_POLNO）索引后的计划，代价更低，实际执行效果更佳，如下图二所示：

两个索引都需要回表，并且回表后需要过滤数据：

643642 -> IX_PREM_INFO_PAYD_AMT_PAY
filter([cast('XXXX', VARCHAR2(1048576 )) = B.POLNO], [B.AMT_TYPE IN (cast('21', VARCHAR2(2 BYTE)), cast('A1', VARCHAR2(2  | BYTE)), cast('2E', VARCHAR2(2 BYTE)))]),
range_key([B.PAYMENT_DATE], [B.AMT_TYPE], [B.PAY_MODE], [B.PK_SERIAL#]), range(MIN ; MAX),                                                         |
range_cond([B.PAYMENT_DATE < :1])

643696 -> IX_PREM_INFO_POLNO  
filter([B.AMT_TYPE IN (cast('21', VARCHAR2(2 BYTE)), cast('A1', VARCHAR2(2 BYTE)), cast('2E', VARCHAR2(2 BYTE)))])
range_key([B.POLNO], [B.PK_SERIAL#]), range(XXXX,MIN ; XXXX,MAX),                                                                |
range_cond([cast('XXXX', VARCHAR2(1048576 )) = B.POLNO

从优化器 trace 中，可以看到索引 643696（IX_PREM_INFO_POLNO）的代价更低，如下图三所示：

但是 [B, C] join 之后，却选择代价高计划 ptr:0x7ef259bb9b20，即B表使用 643642 -> IX_PREM_INFO_PAYD_AMT_PAY。 选择这个计划的原因是 right path dominate left path because of normal nlj。 那么什么是 Normal 计划？ 就是没有条件下压，需要做笛卡尔积然后过滤的 NLJ。 特殊的，会排除掉左边最多只有一行的场景，比如左边是 table get，这种不算 Normal NLJ。当前的策略是 非 Normal NLJ 会裁掉 Normal NLJ。 因此，上面的非条件下压计划被剪裁掉。如下图四所示：

此外，可以根据 ptr:0x7ef259bb9b20 确定连接的表 B，C 分别走的什么索引。并且此处 tables: [B, C] 只是代表这 2 张表做关联，并不代表表是按照 [B, C] 这个顺序关联的。

例如，根据 ptr:0x7ef259bb9b20 过滤到的计划如下图五所示：

关键信息

NLJ 有下压条件，且 Join 表的索引列包含下压条件的列（通常为非等值），命中优化器 非 Normal NLJ 会裁掉 Normal NLJ 规则。

问题原因

当连接可以生成条件下压 NLJ 时，会按规则直接裁剪掉 Normal NLJ 路径。对连接条件为非等值的连接，连接条件通常是不稳定。 原因在于非等值连接条件的连接算子性能非常依赖左右表行数的准确度，一旦左右表的行数远高于估计的行数（无论是由于估行不准，还是由于新增大量的数据），计划的性能都会受大极大的影响。 在这个案例中，同样存在非等值连接，默认选择的条件下压 NLJ 性能很差，而普通 NLJ 路径性能相对较好，但是当前优化器基于规则，默认选择了不优的条件下压 NLJ，从而导致计划不优。 这里的非等值连接通常有 LIKE/NVL/大于/小于（非范围的 ? < xx < ?） 等。

问题的风险及影响

计划走偏，SQL 执行不优。

适用版本

OceanBase 数据库 V4.x 版本。

解决方法

升级到问题已修复版本。目前已修复的版本包含 OceanBase 数据库 V4.2.5 BP6（oceanbase-4.2.5.6-106000052025082216）及之后版本、V4.3.5 BP4（oceanbase-4.3.5.4-104000052025090918）及之后版本。

规避方式

绑定更优的索引，缺乏索引的情况下，需要考虑创建更优的索引。