Db2 把 not in 转换成 anti-join 的规则

首先，我们都知道在 SQL 语义中，not in 和 not exist 是不等价的，尤其是在有 NULL 参与关联判断的时候，关于这方面的说明文档网上有很多，不赘述了。

基于上述原因，大部分数据库都可以将 not exist 转成 anti-join，来获取更好地性能，而 not in 则是在一定条件下，才能转换成 anti-join。在 Db2 中，这种转换是自动打开的，我们看几个例子，首先考虑如下 SQL：

create table t2 (a int,b char(10));
select * from t2 ta where a not in(select tb.a from t2 tb where tb.b='aaa')

获得如下访问计划：

Access Plan:
-----------
	Total Cost: 		73.1943
	Query Degree:		1


            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
             500 
           TBSCAN
           (   2)
           73.1943 
           7.99999 
         /---+----\
        4          1000 
     TBSCAN   TABLE: DB2INST1
     (   3)         T2
     27.3371        Q3
        4 
       |
        4 
     SORT  
     (   4)
     27.3369 
        4 
       |
       40 
     TBSCAN
     (   5)
     27.3356 
        4 
       |
      1000 
 TABLE: DB2INST1
       T2
       Q1

可见 not in 并没有转换成 anti-join。

接下来看看 not exist 会不会转换，语句如下：

select * from t2 ta where not exists(select tb.a from t2 tb where tb.a=ta.a and tb.b='aaa')

得到如下访问计划：

Access Plan:
-----------
	Total Cost: 		54.6607
	Query Degree:		1


              Rows 
             RETURN
             (   1)
              Cost 
               I/O 
               |
              0.001 
            x^HSJOIN
            (   2)
             54.6607 
                8 
         /-----+------\
      1000              40 
     TBSCAN           TBSCAN
     (   3)           (   4)
     27.3128          27.3356 
        4                4 
       |                |
      1000             1000 
 TABLE: DB2INST1  TABLE: DB2INST1
       T2               T2
       Q2               Q1

可见 not exist 是可以自动转换成 anti-join 的。

下面我们试一下，使用 optguideline 强制让 not in 转成 anti-join 会如何，语句如下：

select * from t2 ta where a not in(select tb.a from t2 tb where tb.b='aaa') /* <OPTGUIDELINES><NOTIN2AJ OPTION='ENABLE'/></OPTGUIDELINES> */
SQL0437W  Performance of this complex query might be sub-optimal.  Reason 
code: "13".  SQLSTATE=01602

直接就报了访问计划可能是次优的，看看访问计划：

Access Plan:
-----------
	Total Cost: 		14.6324
	Query Degree:		1


            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
             109 
           TBSCAN
           (   2)
           14.6324 
              2 
         /---+---\
      1.744        218 
     TBSCAN  TABLE: DB2INST1
     (   3)        T2
     6.8323        Q3
        1 
       |
      1.744 
     SORT  
     (   4)
     6.8322 
        1 
       |
      8.72 
     TBSCAN
     (   5)
     6.83185 
        1 
       |
       218 
 TABLE: DB2INST1
       T2
       Q1



Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier: 	1
Diagnostic Details: 	EXP0031W  The optimization guideline NOTIN2AJ has
			not been applied because semantic conditions are
			not met or context is changed.

可以看到，不仅没有 not in 转换成 anti-join，而且还有一段 warning，说明了优化器放弃 NOTIN2AJ 是因为语义条件不满足。

考虑到本文一开始说到的原因，我们测试一下如果在表约束中加上 not null 或者语句中加上 not null 会有什么样的结果。

先重新建表，加个约束：

create table t2 (a int not null,b char(10))
select * from t2 ta where a not in(select tb.a from t2 tb where tb.b='aaa'

访问计划如下：

Access Plan:
-----------
	Total Cost: 		13.6617
	Query Degree:		1


              Rows 
             RETURN
             (   1)
              Cost 
               I/O 
               |
             141.962 
            x^HSJOIN
            (   2)
             13.6617 
                2 
         /-----+------\
       218             8.72 
     TBSCAN           TBSCAN
     (   3)           (   4)
     6.82689          6.83185 
        1                1 
       |                |
       218              218 
 TABLE: DB2INST1  TABLE: DB2INST1
       T2               T2
       Q2               Q1

已经可以将 not in 转换成 anti-join 了，即使不加 optguideline 也可以。

换一种方式，不加表约束，在语句中加上 not null：

create table t2 (a int,b char(10))
select * from t2 ta where a not in(select tb.a from t2 tb where tb.b='aaa' and tb.a is not null)

访问计划：

Access Plan:
-----------
	Total Cost: 		20.511
	Query Degree:		1


                        Rows 
                       RETURN
                       (   1)
                        Cost 
                         I/O 
                         |
                       145.003 
                      x^HSJOIN
                      (   2)
                       20.511 
                          3 
               /---------+---------\
             218                   8.3712 
           TBSCAN                  TBSCAN
           (   3)                  (   5)
           13.6721                 6.83595 
              2                       1 
         /---+----\                  |
     8.3712         218              218 
     TBSCAN   TABLE: DB2INST1  TABLE: DB2INST1
     (   4)         T2               T2
     6.83595        Q4               Q1
        1 
       |
       218 
 TABLE: DB2INST1
       T2
       Q2

效果也是一样的。

最后我们试一下本应该能将 not in 转换成 anti-join 的场景，通过 optguideline 来禁用它，也就是如下 SQL：

create table t2 (a int not null,b char(10))
select * from t2 ta where a not in(select tb.a from t2 tb where tb.b='aaa') /* <OPTGUIDELINES><NOTIN2AJ OPTION='DISABLE'/></OPTGUIDELINES> */

访问计划：

Access Plan:
-----------
	Total Cost: 		73.1943
	Query Degree:		1


            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
             500 
           TBSCAN
           (   2)
           73.1943 
           7.99999 
         /---+----\
        4          1000 
     TBSCAN   TABLE: DB2INST1
     (   3)         T2
     27.3371        Q3
        4 
       |
        4 
     SORT  
     (   4)
     27.3369 
        4 
       |
       40 
     TBSCAN
     (   5)
     27.3356 
        4 
       |
      1000 
 TABLE: DB2INST1
       T2
       Q1

可以看到，这里没有改写成 anti-join

结论：

1. 由于 not in 和 not exist 语义上的不同（主要是对待 null 值），not exist 可以使用 anti-join 访问计划，not in 在不满足条件的情况下不行
2. 如果不满足条件，即使强行加上 optguideline，优化器依然无法使用将 not in 转换成 anti-join
3. 如果在表约束或者语句上加上关联字段 is not null 的限制条件，即使不使用 optguideline，优化器也可以自动将 not in 转换成 anti-join
4. 如果条件满足，本应该能将 not in 转换成 anti-join 的场景，可以通过 optguideline 来禁用它。