本文使用数据库版本：

• DM7：V7.1.5.121
• MySQL：5.5.54

DM7 的优化器提示参数可以在配置文件 dm.ini 中设置，可以使用 alter session set 语句指定，也可以使用伪注释 /*+ … */ 的方式指定。三种方式设置的参数所影响的范围不一样，如在 dm.ini 中设置则影响整个系统，而伪注释则仅影响涉及的语句。一个基本的原则是影响范围越小则优先级别越高。

为了方便，这里所有的例子都采用伪注释的方式指定。

REFED_EXISTS_OPT_FLAG 用于控制 exists 谓词 + 相关子查询 (correlated subquery) 的变换方式，取值为 0 或者 1，当前的 DM7 版本缺省设置为 0，即关闭状态。打开这个参数时 （REFED_EXISTS_OPT_FLAG = 1），则优化器会试图把 exists 的相关子查询变 in + 非相关子查询。

先稍微解释一下什么是 Exists 量（谓）词。Exists 是 SQL 的基本特征之一，它表示存在量词：设 x 是一个变量，P(x) 表示一个谓词或者说一个布尔表达式，则 Exists(x) P(x) 为真，当且仅当对变量 x 的某一个值，P(x) 为真。例如：设 1 < x < 10, P(x) = x <5，则 Exists(x) (x <5) 为真；若 P(x)= x < 0, 则 Exists (x) (x < 0) 为假。这里的解释有点抽象。在 SQL 中，通常使用 Exists(select * from T where 相关布尔表达式 P(x) 的方式来表示。因为需要扫描x的定义域进行探测，所以最坏情况下有可能需要对每一个取值都遍历一遍，效率很低，因此在 SQL 中对 Exists 的优化是不可避免的。

下面举个简单的例子，创建两个表，并在 t1 中插入 99999 记录，t2 中插入 99 行记录。注意记录数的差别。产生记录的方式采用了一点 Oracle DBA 常用的小技巧。

create table t1(c1 int,c2 int);

create table t2(d1 int,d2 int);

insert into t1 select level, level * 10
    from dual connect by level < 100000;

insert into t2 select level, level *10
    from dual connect by level <100;

然后观察下面这个查询优化器输出的执行计划：

这个查询的意思是对 t1 的每一行r，如果在 t2 中存在一行使得 t2.d1 和 r.c1 相等，则把 r 加入结果集 R，否则丢弃 r, 最后获得结果 R。显然我们应该获得 99 行结果。

得益于 DM7 强大的 hash join 能力，这个执行计划还是不错的。它先扫描小表 t2 创建一个 hash 表，然后一趟扫描大表 t1，对每一行 t1.r 在 hash 表中探测，如果命中则向上输出 t1.r。因为输出的是 HASH JOIN 的数据，因此这个 HASH JOIN 是 HASH RIGHT SEMI JOIN。DM7 的扫描和hash join 都非常快，执行这个查询大概需要若干毫秒。 MySQL 的用户可以在 MySQL 上尝试一下这个查询，即使在 t2 的 d1 上创建一个索引，也需要对这个子查询执行约 10 万次，性能应该比较慢。所以不支持 hash join 使得限制了 MySQL 优化器的手脚。

这里有个问题，如果 t1.c1 上有一个索引，那么情况有会如何呢？

create index t1101 on t1(c1);

仔细观察这个查询，我们可以发现，其实只要扫描 t2, 利用 t2.d1 作为 key, 在索引 t1l01 中查找，如果有对应的记录则把相应的 t1.r 加入结果集。这样我们只要做 99 个索引探测即可，不需要 hash join, 也不需要对大表 t1 的全表扫描了。这就是 REFED_EXISTS_OPT_FLAG 的作用！

我们可以打开 REFED_EXISTS_OPT_FLAG, 让 DM7 的优化器尝试做这个变换：

果然，优化器输出了一个嵌套循环的索引连接。它先扫描只有 99 行的 t2，做一个去重 (distinct) 操作，以防止重复探测，然后在 T1L01 中进行 2 级别索引 seek, 这个执行逻辑正是前面所分析的。它的执行速度非常快，时间缩短很多。

打开 REFED_EXISTS_OPT_FLAG 后消除了 HASH, 因此 MySQL 其实也可以利用这样的计划。笔者在 MySQL 5.5 上做了试验，即使创建了 t1l01 这样的索引，它仍然输出下面这个计划：

MySQL 老老实实扫描 t1, 然后取出 t1.r, 代入子查询计算，总共做约 10 万次子查询运算。

回到 DM7，事实上这个参数打开后，优化器把上面这个查询变换为等价的 IN (非相关子查询）：

select * from t1 where c1 in (select d1 from t2);

如果相关子查询无法变换成非相关的子查询，则即使打开这个参数，优化器也可能因为找不到合适的变换形式而放弃。另外，对于 not exists, 因为涉及到 NULL 的逻辑不同，和 not in 不等价，因此也不能转换。