DM数据库ENABLE_DIST_IN_SUBQUERY_OPT参数详解

ENABLE_DIST_IN_SUBQUERY_OPT参数详解

此参数的核心功能是控制优化器是否对 IN 子查询执行“去子查询”的优化操作。

当取值为 0 时：关闭优化。SQL 语句中的 IN 子查询将保留其子查询结构，通常以 半连接 的形式在执行计划中出现。

当取值为 1 时：开启优化。优化器会尝试将符合条件的 IN 子查询移除，并将子查询内部的过滤条件提升到外层查询中，从而简化查询结构，通常能显著提升执行效率。

创建测试表

DROP TABLE T1; DROP TABLE T2; CREATE TABLE T1(C1 INT UNIQUE, C2 INT); CREATE TABLE T2(D1 INT, D2 INT);

(ENABLE_DIST_IN_SUBQUERY_OPT为0)创建测试sql

EXPLAIN SELECT /*+ ENABLE_DIST_IN_SUBQUERY_OPT(0) */ * FROM T1, T2 WHERE C1 IN (SELECT C1 FROM T1 WHERE C2<5)AND D1=C1AND D2=5; 1 #NSET2: [1, 1, 16] 2 #PRJT2: [1, 1, 16]; exp_num(4), is_atom(FALSE) 3 #INDEX JOIN SEMI JOIN2: [1, 1, 16]; join condition(T1.C2 < 5), flt_batch_exec(0) 4 #HASH2 INNER JOIN: [1, 1, 16]; RKEY_UNIQUE KEY_NUM(1); KEY(T2.D1=T1.C1) KEY_NULL_EQU(0) 5 #NEST LOOP INDEX JOIN2: [1, 1, 16] 6 #ACTRL: [1, 1, 16] 7 #SLCT2: [1, 1, 8]; T2.D2 = 5 8 #CSCN2: [1, 1, 8]; INDEX33555629(T2); btr_scan(1) 9 #BLKUP2: [1, 1, 4]; INDEX33555628(T1) 10 #SSEK2: [1, 1, 4]; scan_type(ASC), INDEX33555628(T1), scan_range[T2.D1,T2.D1], is_global(0) 11 #CSCN2: [1, 1, 8]; INDEX33555627(T1); btr_scan(1) 12 #BLKUP2: [1, 1, 4]; INDEX33555628(T1) 13 #SSEK2: [1, 1, 4]; scan_type(ASC), INDEX33555628(T1), scan_range[T1.C1,T1.C1], is_global(0)

保留了子查询结构。执行计划中出现了一个独立的 #INDEX JOIN SEMI JOIN2（索引半连接）节点，专门用于处理 IN 子查询的逻辑。。

(ENABLE_DIST_IN_SUBQUERY_OPT为1)创建测试sql

EXPLAIN SELECT /*+ ENABLE_DIST_IN_SUBQUERY_OPT(1) */ * FROM T1, T2 WHERE C1 IN (SELECT C1 FROM T1 WHERE C2<5)AND D1=C1AND D2=5; 1 #NSET2: [1, 1, 16] 2 #PRJT2: [1, 1, 16]; exp_num(4), is_atom(FALSE) 3 #SLCT2: [1, 1, 16]; (NOT(T1.C1 IS NULL) AND T1.C2 < 5) 4 #HASH2 INNER JOIN: [1, 1, 16]; RKEY_UNIQUE KEY_NUM(1); KEY(T2.D1=T1.C1) KEY_NULL_EQU(0) 5 #SLCT2: [1, 1, 16]; T1.C2 < 5 6 #NEST LOOP INDEX JOIN2: [1, 1, 16] 7 #ACTRL: [1, 1, 16] 8 #SLCT2: [1, 1, 8]; T2.D2 = 5 9 #CSCN2: [1, 1, 8]; INDEX33555629(T2); btr_scan(1) 10 #BLKUP2: [1, 1, 4]; INDEX33555628(T1) 11 #SLCT2: [1, 1, 4]; NOT(T1.C1 IS NULL) 12 #SSEK2: [1, 1, 4]; scan_type(ASC), INDEX33555628(T1), scan_range[T2.D1,T2.D1], is_global(0) 13 #CSCN2: [1, 1, 8]; INDEX33555627(T1); btr_scan(1)

消除了子查询节点。原属于子查询内部的过滤条件 T1.C2 < 5 被直接提升到了外层查询中，变成了普通的表连接与过滤操作（见计划第3行和第5行的 #SLCT2）。

TOP_ORDER_OPT_FLAG取值应用场景解析

ENABLE_DIST_IN_SUBQUERY_OPT = 1

减少计算开销：消除了 SEMI JOIN 这一额外的算子层级。半连接本身需要额外的内存和 CPU 资源来维护状态并去重，将其转换为普通的内连接和过滤可以显著降低这些开销。

更早地过滤数据：条件 T1.C2 < 5 被提取到外层后，可以在 Hash Join 发生之前（甚至在与 T2 关联时）就尽早过滤掉不符合条件的数据，减少了参与后续连接的中间结果集大小。

ENABLE_DIST_IN_SUBQUERY_OPT = 0

执行链路长：多出了 SEMI JOIN 及其相关的回表 (BLKUP2) 和索引扫描 (SSEK2) 节点，增加了 I/O 和 CPU 的消耗。

适用性局限：这种保留子查询的方式通常是在无法满足“去子查询”严格条件时的兜底策略。在当前测试场景中，由于满足优化条件却强制关闭，导致选择了次优的执行路径。