当查询语句中包含子查询，且执行计划使用 Subplan Filter 算子来执行子查询的访问，如果 Subplan Filter 下面的算子包含 Nested Loop Join，很可能因为无法使用 Batch Rescan 而导致性能问题。
详细说明
什么是 Batch Rescan ？
Batch Rescan 是 Nested Loop Join（和 Subplan Filter）的一种优化执行方式。Nested Loop Join 算法每次从驱动表中取一条记录，根据 Join 条件去关联被驱动表，查找符合条件的记录；驱动表的记录有多少，被驱动表就要被关联查询多少次。当驱动表的记录有很多时，执行关联查询的代价就会比较高，特别是当被驱动表是在另外一台服务器上时，Nest Loop Join 的性能会变得更差。为了提升 Nested Loop Join 的性能，OceanBase 数据库可以使用 Batch Rescan，一次从驱动表读取一批记录，而不是一条记录，去关联被驱动表，这样就大幅降低了关联查询的次数。
怎么判断 Nest-Loop Join 是否使用了 Batch Rescan ？
当执行计划里有 Nested Loop Join 或者 Subplan Filter 算子，并且对应算子的 use_batch=true，则说明使用了 Batch Rescan。
示例如下。 下面的 UPDATE 语句在 SET 语句和 EXIST 语句中均包含子查询：
update tbz "Z"
set
("Z"."F_TERMVALIDNUM", "Z"."F_TERMVALIDBALANCE") = (
select "S"."CUSTS", "S"."F_SUMBALANCE"
from tbs "S"
where
("S"."C_FLAG" = 'VALID')
and ("S"."MONTHID" = "Z"."MONTHID")
and ("S"."C_CHANNELNO" = "Z"."C_CHANNELNO")
and ("S"."C_AGENCYNO" = "Z"."C_AGENCYNO")
and ("S"."C_FUNDCODE" = "Z"."C_FUNDCODE")
and ("S"."C_TANO" = "Z"."C_TANO")
and ("S"."MONTHID" = 202303)
)
where
exists(
(
select 1
from tbs "S"
where
("S"."C_FLAG" = 'VALID')
and ("S"."MONTHID" = "Z"."MONTHID")
and ("S"."C_CHANNELNO" = "Z"."C_CHANNELNO")
and ("S"."C_AGENCYNO" = "Z"."C_AGENCYNO")
and ("S"."C_FUNDCODE" = "Z"."C_FUNDCODE")
and ("S"."C_TANO" = "Z"."C_TANO")
and ("S"."MONTHID" = 202303)
)
)
and ("Z"."MONTHID" = 202303)
and ("Z"."YEARID" = 2023);
SQL 对应的执行计划如下：
==============================================================================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
------------------------------------------------------------------------------------------------------------------------------
|0 |DISTRIBUTED UPDATE | |1 |6497 |0 |0 |0 |0 |
|1 |└─SUBPLAN FILTER | |1 |6471 |3695 |0 |0 |0 |
|2 | ├─NESTED-LOOP JOIN | |1 |2585 |3696 |0 |0 |0 |
|3 | │ ├─SUBPLAN SCAN |VIEW2 |1 |2566 |14310 |0 |0 |0 |
|4 | │ │ └─HASH DISTINCT | |1 |2566 |14310 |0 |0 |0 |
|5 | │ │ └─TABLE FULL SCAN |S(IDX_TEMP_DQDE_CUSTS)|1 |2566 |14310 |0 |0 |0 |
|6 | │ └─DISTRIBUTED TABLE GET|Z |1 |19 |3696 |1412331562 |0 |0 |
|7 | └─TABLE FULL SCAN |S(IDX_TEMP_DQDE_CUSTS)|1 |3886 |3695 |1412210335 |0 |0 |
==============================================================================================================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil)
1 - output(...), filter(nil)
exec_params_([Z.MONTHID(:13)], [Z.C_CHANNELNO(:14)], [Z.C_AGENCYNO(:15)], [Z.C_FUNDCODE(:16)], [Z.C_TANO(:17)]), onetime_exprs_(nil), init_plan_idxs_(nil),
use_batch=false
2 - output(...), filter(nil)
conds(nil), nl_params_([VIEW2.VIEW1.S.C_CHANNELNO(:22)], [VIEW2.VIEW1.S.C_AGENCYNO(:23)], [VIEW2.VIEW1.S.C_FUNDCODE(:24)], [VIEW2.VIEW1.S.C_TANO(:25)]),
use_batch=false
...
执行计划中，Nested Loop Join 算子和 Subplan Filter 算子对应的 use_batch =false，说明均未能使用 Batch Rescan。
优化建议
是否使用 Batch Rescan 是由数据库自己决定的，不同的 OceanBase 数据库版本下 Batch Rescan 支持的场景用不同。在 OceanBase 数据库 V4.2.1 版本，示例的 SQL 无法在 Subplan Filter 中使用 Batch Rescan，而在 V4.2.5 版本则可以。 如果数据库版本不支持使用 Batch Rescan，优化的方式是用其他的 Join 方式来代替 Nested Loop Join，比如使用 Hash Join。对于示例中的 UPDATE，对应的 Hint 如下：
/*+ LEADING(VIEW2 Z) USE_HASH(Z) */
此外，针对示例中的 UPDATE 语句，还可以将 UPDATE 改写为 MERGE 来手动消除子查询的存在，从而提升性能。
merge into tbz z
using tbs s on
( ("S"."C_FLAG" = 'VALID')
and ("S"."MONTHID" = "Z"."MONTHID")
and ("S"."C_CHANNELNO" = "Z"."C_CHANNELNO")
and ("S"."C_AGENCYNO" = "Z"."C_AGENCYNO")
and ("S"."C_FUNDCODE" = "Z"."C_FUNDCODE")
and ("S"."C_TANO" = "Z"."C_TANO")
and ("S"."MONTHID" = 202303)
)
when matched then update set "Z"."F_TERMVALIDNUM"="S"."CUSTS", "Z"."F_TERMVALIDBALANCE"= "S"."F_SUMBALANCE"
where ("Z"."MONTHID" = 202303) and ("Z"."YEARID" = 2023);
适用版本
OceanBase 数据库 V4.x 版本。
