OB中NOT EXISTS一定要改写吗？

背景：

我前段时间写了一篇《 关于OB中左外连接和反连接的探究 》的文章，后来官网知识库也更新了这部分的内容。链接如下。

https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000475695?back=kb

所以not exists在ob中就不建议使用，或者说not exists只能通过改写去优化吗？

当然不是这样的，包括我文章的介绍也只是能说明batch rescan的优化比无法使用该特性的anti join性能好，并不是说只能通过这种方式才能优化。

我还是习惯用实验的方式来说明，下面我还是用我上一篇文章的sql做个优化实验。

实验过程：

sql原文及效率和执行计划如下，因为CTM_GM是视图，所以执行计划中看到的表名是SD_CTM_GM。

#####sql文本
 select  count(1)
    from tttt.mmmmm_sssssale t
   where t.sssss  not in ('e111', 'ddddda')
     and t.stats  = '1'
     and t.parean  is null
     and t.city  = 2208
     AND (t.cusystatus  = 'FFFFGGGGG')
     AND (T.ORGGGGGGNEL  is null or T.ORGGGGGGNEL  != 'infonow')
      AND NOT EXISTS (SELECT  1
            FROM tttt.TTTT_OWN_C  TOW
           WHERE T.PID  = TOW.OID
             AND TOW.CT_ID  = T.city 
             AND TOW.OODDD_sTS   IN
                 (SELECT DDDC
                    FROM tttt.CTM_GM
                   WHERE GPID  = 'OtherThing '
       AND stats  = '1'))  and to_char(createdate,'yyyymmdd') between 20150101 and 202301211;	
###执行时间
 +----------+
| COUNT(1) |
+----------+
|    29493 |
+----------+
1 row in set (43.87 sec)
####执行计划
| ===========================================================================================
|ID|OPERATOR                           |NAME                             |EST. ROWS|COST  |
-------------------------------------------------------------------------------------------
|0 |SCALAR GROUP BY                    |                                 |1        |161064|
|1 | NESTED-LOOP ANTI JOIN             |                                 |808      |161033|
|2 |  TABLE SCAN                       |T                                |1278     |40623 |
|3 |  PX COORDINATOR                   |                                 |1        |94    |
|4 |   EXCHANGE OUT DISTR              |:EX10001                         |1        |94    |
|5 |    SUBPLAN SCAN                   |VIEW2                            |1        |94    |
|6 |     NESTED-LOOP JOIN              |                                 |1        |94    |
|7 |      EXCHANGE IN DISTR            |                                 |1        |92    |
|8 |       EXCHANGE OUT DISTR (BC2HOST)|:EX10000                         |1        |92    |
|9 |        TABLE SCAN                 |TOW(IDX_TTTT_OWN_C _ORDERID)     |1        |92    |
|10|      TABLE SCAN                   |SD_CTM_GM(PK_SD_CTM_GM)          |1        |32    | 
===========================================================================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(*)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(*)])
  1 - output([1]), filter(nil), 
      conds(nil), nl_params_([T.PID ])
  2 - output([T.PID ]), filter([T.city  = 2208], [cast(cast(TO_CHAR(T.CREATEDATE, ?), VARCHAR2(256 BYTE)), NUMBER(-1, -85)) >= 20150101], [cast(cast(TO_CHAR(T.CREATEDATE, ?), VARCHAR2(256 BYTE)), NUMBER(-1, -85)) <= 202301211], [(T_OP_IS, T.ORGGGGGGNEL , NULL, 0) OR T.ORGGGGGGNEL  != ?], [(T_OP_NOT_IN, T.sssss , (?, ?))], [(T_OP_IS, T.parean , NULL, 0)], [T.stats  = ?], [T.cusystatus  = ?]), 
      access([T.sssss ], [T.stats ], [T.parean ], [T.city ], [T.cusystatus ], [T.ORGGGGGGNEL ], [T.PID ], [T.CREATEDATE]), partitions(p0)
  3 - output([1]), filter(nil)
  4 - output([1]), filter(nil), is_single, dop=1
  5 - output([1]), filter(nil), 
      access([VIEW2.TOW.OID])
  6 - output([TOW.OID]), filter(nil), 
      conds(nil), nl_params_([TOW.OODDD_sTS  ])
  7 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil)
  8 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil), is_single, dop=1
  9 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter([TOW.CT_ID  = 2208]), 
      access([TOW.OID], [TOW.CT_ID ], [TOW.OODDD_sTS  ]), partitions(p0)
  10 - output([1]), filter([SD_CTM_GM.stats  = ?]), 
      access([SD_CTM_GM.stats ]), partitions(p0)

可以看到该sql走了NESTED-LOOP ANTI JOIN，执行时间是43s，执行时间比较长。

按照正常的优化思路来分析下，咱们先看下实际的数据量。

obclient>  select  count(1)
    ->     from tttt.mmmmm_sssssale t
    ->    where t.sssss  not in ('e111', 'ddddda')
    ->      and t.stats  = '1'
    ->      and t.parean  is null
    ->      and t.city  = 2208
    ->      AND (t.cusystatus  = 'FFFFGGGGG')
    ->      AND (T.ORGGGGGGNEL  is null or T.ORGGGGGGNEL  != 'infonow') and to_char(createdate,'yyyymmdd') between 20150101 and 202301211; 
+----------+
| COUNT(1) |
+----------+
|    29493 |
+----------+
1 row in set (0.08 sec)

obclient> SELECT count(*)
    ->                     FROM tttt.CTM_GM
    ->                    WHERE GPID  = 'OtherThing '
    ->        AND stats  = '1' ;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

obclient> SELECT count(*) from  tttt.TTTT_OWN_C  TOW;
+----------+
| COUNT(*) |
+----------+
|  5087140 |
+----------+
1 row in set (1.99 sec)

可以看到CTM_GM的结果是0行，扫描速度很快，那么not exists的子查询结果也是0行，但是TOW表有500W数据，原本的执行计划是TOW通过nl连接CTM_GM，要取TOW的结果集去匹配CTM_GM，理论上我们修改这两个表关联顺序，就可以只取CTM_GM的0行消除掉TOW表这么大数据量的消耗代价。

######sql文本
select /*+use_nl(@"SEL$1" ("VIEW2"@"SEL$1" ))*/ count(1)
    from tttt.mmmmm_sssssale t
   where t.sssss  not in ('e111', 'ddddda')
     and t.stats  = '1'
     and t.parean  is null
     and t.city  = 2208
     AND (t.cusystatus  = 'FFFFGGGGG')
     AND (T.ORGGGGGGNEL  is null or T.ORGGGGGGNEL  != 'infonow')
      AND NOT EXISTS (SELECT  /*+leading(CTM_GM) use_nl(CTM_GM,TOW) */ 1
            FROM tttt.TTTT_OWN_C  TOW
           WHERE T.PID  = TOW.OID
             AND TOW.CT_ID  = T.city 
             AND TOW.OODDD_sTS   IN
                 (SELECT DDDC
                    FROM tttt.CTM_GM
                   WHERE GPID  = 'OtherThing '
       AND stats  = '1'))  and to_char(createdate,'yyyymmdd') between 20150101 and 202301211;		

#######执行效率
+----------+
| COUNT(1) |
+----------+
|    29493 |
+----------+
1 row in set (0.21 sec)	  


####执行计划

| =========================================================================================
|ID|OPERATOR                |NAME                                      |EST. ROWS|COST  |
-----------------------------------------------------------------------------------------
|0 |SCALAR GROUP BY         |                                          |1        |275986|
|1 | NESTED-LOOP ANTI JOIN  |                                          |808      |275955|
|2 |  PX COORDINATOR        |                                          |1278     |41514 |
|3 |   EXCHANGE OUT DISTR   |:EX10000                                  |1278     |40623 |
|4 |    TABLE SCAN          |T                                         |1278     |40623 |
|5 |  SUBPLAN SCAN          |VIEW2                                     |1        |183   |
|6 |   NESTED-LOOP JOIN     |                                          |1        |183   |
|7 |    TABLE SCAN          |SD_CTM_GM(INX_SD_CTM_GM_GPID )|1        |92    |
|8 |    MATERIAL            |                                          |1        |92    |
|9 |     PX COORDINATOR     |                                          |1        |92    |
|10|      EXCHANGE OUT DISTR|:EX20000                                  |1        |92    |
|11|       TABLE SCAN       |TOW(IDX_TTTT_OWN_C _ORDERID)            |1        |92    |
=========================================================================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(*)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(*)])
  1 - output([1]), filter(nil), 
      conds(nil), nl_params_([T.PID ])
  2 - output([T.PID ]), filter(nil)
  3 - output([T.PID ]), filter(nil), is_single, dop=1
  4 - output([T.PID ]), filter([T.city  = 2208], [cast(cast(TO_CHAR(T.CREATEDATE, ?), VARCHAR2(256 BYTE)), NUMBER(-1, -85)) >= 20150101], [cast(cast(TO_CHAR(T.CREATEDATE, ?), VARCHAR2(256 BYTE)), NUMBER(-1, -85)) <= 202301211], [(T_OP_IS, T.ORGGGGGGNEL , NULL, 0) OR T.ORGGGGGGNEL  != ?], [(T_OP_NOT_IN, T.sssss , (?, ?))], [(T_OP_IS, T.parean , NULL, 0)], [T.stats  = ?], [T.cusystatus  = ?]), 
      access([T.sssss ], [T.stats ], [T.parean ], [T.city ], [T.cusystatus ], [T.ORGGGGGGNEL ], [T.PID ], [T.CREATEDATE]), partitions(p0)
  5 - output([1]), filter(nil), 
      access([VIEW2.TOW.OID])
  6 - output([TOW.OID]), filter(nil), 
      conds([TOW.OODDD_sTS   = SD_CTM_GM.DDDC]), nl_params_(nil)
  7 - output([SD_CTM_GM.DDDC]), filter([SD_CTM_GM.stats  = ?]), 
      access([SD_CTM_GM.stats ], [SD_CTM_GM.DDDC]), partitions(p0)
  8 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil)
  9 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil)
  10 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil), is_single, dop=1
  11 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter([TOW.CT_ID  = 2208]), 
      access([TOW.OID], [TOW.CT_ID ], [TOW.OODDD_sTS  ]), partitions(p0) 

通过添加hint的方式我们可以看到我只调整了TOW和CTM_GM的连接顺序，效率从43s优化到了0.21s，而我上一篇文章中改写之后的效率是1s，所以该sql不需要改写就可以优化掉。

那这个效率还可以优化吗，看起来还有优化空间，既然not exists的结果集是0,那我用上面同样的方式，干预下view结果集和T表的顺序，能不能得到更好的结果那？

####sql文本
select /*+leading(("VIEW2"@"SEL$1" ))*/ count(1)
    from tttt.mmmmm_sssssale t
   where t.sssss  not in ('e111', 'ddddda')
     and t.stats  = '1'
     and t.parean  is null
     and t.city  = 2208
     AND (t.cusystatus  = 'FFFFGGGGG')
     AND (T.ORGGGGGGNEL  is null or T.ORGGGGGGNEL  != 'infonow')
      AND NOT EXISTS (SELECT  /*+leading(CTM_GM) use_nl(CTM_GM,TOW) */ 1
            FROM tttt.TTTT_OWN_C  TOW
           WHERE T.PID  = TOW.OID
             AND TOW.CT_ID  = T.city 
             AND TOW.OODDD_sTS   IN
                 (SELECT DDDC
                    FROM tttt.CTM_GM
                   WHERE GPID  = 'OtherThing '
       AND stats  = '1'))  and to_char(createdate,'yyyymmdd') between 20150101 and 202301211;		  
####执行效率	   
+----------+
| COUNT(1) |
+----------+
|    29493 |
+----------+
1 row in set (0.08 sec)
#########执行计划	   
| ==========================================================================================
|ID|OPERATOR                |NAME                                      |EST. ROWS|COST   |
------------------------------------------------------------------------------------------
|0 |SCALAR GROUP BY         |                                          |1        |2231317|
|1 | HASH RIGHT ANTI JOIN   |                                          |808      |2231286|
|2 |  SUBPLAN SCAN          |VIEW2                                     |470      |2188504|
|3 |   NESTED-LOOP JOIN     |                                          |470      |2188497|
|4 |    TABLE SCAN          |SD_CTM_GM(INX_SD_CTM_GM_GPID )|1        |92     |
|5 |    MATERIAL            |                                          |299244   |2182932|
|6 |     PX COORDINATOR     |                                          |299244   |2169701|
|7 |      EXCHANGE OUT DISTR|:EX10000                                  |299244   |2075029|
|8 |       TABLE SCAN       |TOW                                       |299244   |2075029|
|9 |  PX COORDINATOR        |                                          |1278     |41514  |
|10|   EXCHANGE OUT DISTR   |:EX20000                                  |1278     |40623  |
|11|    TABLE SCAN          |T                                         |1278     |40623  |
==========================================================================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(*)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(*)])
  1 - output([1]), filter(nil), 
      equal_conds([T.PID  = VIEW2.TOW.OID]), other_conds(nil)
  2 - output([VIEW2.TOW.OID]), filter(nil), 
      access([VIEW2.TOW.OID])
  3 - output([TOW.OID]), filter(nil), 
      conds([TOW.OODDD_sTS   = SD_CTM_GM.DDDC]), nl_params_(nil)
  4 - output([SD_CTM_GM.DDDC]), filter([SD_CTM_GM.stats  = ?]), 
      access([SD_CTM_GM.stats ], [SD_CTM_GM.DDDC]), partitions(p0)
  5 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil)
  6 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil)
  7 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter(nil), is_single, dop=1
  8 - output([TOW.OID], [TOW.OODDD_sTS  ]), filter([TOW.CT_ID  = 2208]), 
      access([TOW.OID], [TOW.CT_ID ], [TOW.OODDD_sTS  ]), partitions(p0)
  9 - output([T.PID ]), filter(nil)
  10 - output([T.PID ]), filter(nil), is_single, dop=1
  11 - output([T.PID ]), filter([T.city  = 2208], [cast(cast(TO_CHAR(T.CREATEDATE, ?), VARCHAR2(256 BYTE)), NUMBER(-1, -85)) >= 20150101], [cast(cast(TO_CHAR(T.CREATEDATE, ?), VARCHAR2(256 BYTE)), NUMBER(-1, -85)) <= 202301211], [(T_OP_IS, T.ORGGGGGGNEL , NULL, 0) OR T.ORGGGGGGNEL  != ?], [(T_OP_NOT_IN, T.sssss , (?, ?))], [(T_OP_IS, T.parean , NULL, 0)], [T.stats  = ?], [T.cusystatus  = ?]), 
      access([T.sssss ], [T.stats ], [T.parean ], [T.city ], [T.cusystatus ], [T.ORGGGGGGNEL ], [T.PID ], [T.CREATEDATE]), partitions(p0)	   

可以看到现在效率0.08s比一开始的43s提升了500多倍比单纯的改写效率提升了12倍多，所以NOT EXISTS不一定需要改写，可能只是计划走错了，这种情况下我们绑定下计划就好了，很多时候应用改代码的代价也很大。

结论：

很多sql不是一定要去改写才能解决的，虽然改写有可能可以使用到一些优化算子，但是可能问题的根因不在这里，我们一定要提高自己分析问题的能力，准确判断分析问题，这样在工作中可以得心应手，也可以避免很多不必要的冗余的工作。

行之所向，莫问远方。