Oracle NOT IN反连接中null值的处理和疑问
Oracle NOT IN反连接中null值的处理和疑问
若有sql使用not in写法如下：
其中t和tt表的col列没有not null限制，这个时候一旦子查询中出现null值，会导致整个sql没有结果输出。
select count(*) from t where t.col not in (select tt.col from tt);
为啥，比如where id not in (1,2,null)，实际上等价于where id!=1 and id!=2 and id!=null，而其中表达式id!=null永不为真，导致一整个表达式也永不为真，因此null值在not in里边会导致sql无结果。
这就是说，not in (子查询)，子查询中一定要带is not null限制才有意思。上诉例子子查询应该为select tt.col from tt where tt.col is not null。
在11g之前，如果not in可能出现null值，则无法进行反连接优化，只能进行filter操作（即无法子查询展开）。
测试表信息如下：
15:16:18 ZKM@zkm(37)> desc t 15:16:20 ZKM@zkm(37)> desc tt Name Null? Type Name Null? Type ------- -------- ------------- ------- -------- ------------- COL VARCHAR2(20) COL VARCHAR2(20) 15:17:40 ZKM@zkm(37)> select col,count(*) from t group by col; 08:17:43 ZKM@zkm(37)> select col,count(*) from tt group by col; COL COUNT(*) COL COUNT(*) ------ ---------- ------ ---------- 1000000 1000001 a 1 Elapsed: 00:00:00.16 Elapsed: 00:00:00.00
模拟10g情景：
alter session set optimizer_features_enable='10.2.0.4'; or alter session set "_optimizer_null_aware_antijoin" = false; select count(*) from t where t.col not in (select tt.col from tt); Plan hash value: 2833190650 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.16 | 1578 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.16 | 1578 | |* 2 | FILTER | | 1 | | 0 |00:00:00.16 | 1578 | | 3 | TABLE ACCESS FULL| T | 1 | 201M| 1000K|00:00:00.07 | 1574 | |* 4 | TABLE ACCESS FULL| TT | 1 | 1 | 1 |00:00:00.01 | 4 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( IS NULL) 4 - filter(LNNVL("TT"."COL"<>:B1))
11g后，会进行NULL-Aware反连接优化。
所谓NULL-Aware Antijoin，根据Cost-Based Optimizer NULL-Aware Antijoin (文档 ID 1082123.1)可得知，
NULL-Aware Antijoin 是一种功能，它试图解决与 NULL 值和子查询展开的各种问题。通常，如果子查询的连接条件可能为 NULLable，则子查询展开是不可能的。
而NULL-Aware Antijoin则可以按如下方式实现连接子查询展开后的操作：（按照mos文档说的这个步骤，必须首先先访问次表TT，那么表TT就只能作为驱动表）
从tt表检索行，并在tt表上应用过滤器谓词，即where过滤数据，不过我这里没有对tt做where。
如果 tt 上的过滤谓词的结果不包含任何行，则为 t 的每一行返回 t.col，并终止。
如果 tt.col 包含至少一个 NULL 值，则不返回 t 的任何行，并终止。
如果 t.col 与 tt.col 的任何值都不匹配且t.col 为非 NULL，则返回 t.col 以获取 t 中的一行。
另外，有关NULL-Aware Antijoin的执行路径有(ANTI SNA表示"single null-aware antijoin." ANTI NA表示"null-aware antijoin.")：
- HASH JOIN ANTI NA
- HASH JOIN RIGHT ANTI NA
- HASH JOIN ANTI SNA
- HASH JOIN RIGHT ANTI SNA
因此，在11g之后，当not in只要有可能出现null，同时驱动表为tt的情况下，一旦tt表只要检索出null值，立刻结束sql并返回0行结果。同时执行路径为“HASH JOIN RIGHT ANTI NA”。
这里tt表全表扫描的第一条数据正是null，整个sql执行过程只有3个逻辑读。
select count(*) from t where t.col not in (select tt.col from tt); Plan hash value: 2805095820 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | | |* 2 | HASH JOIN RIGHT ANTI NA| | 1 | 201M| 0 |00:00:00.01 | 3 | 1888K| 1888K| 464K (0)| | 3 | TABLE ACCESS FULL | TT | 1 | 2 | 1 |00:00:00.01 | 3 | | | | | 4 | TABLE ACCESS FULL | T | 0 | 201M| 0 |00:00:00.01 | 0 | | | | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T"."COL"="TT"."COL")
但是如果驱动表是t表，执行路径则是"HASH JOIN ANTI NA"，并且会先全表扫描t表。
此时虽然t表实际值均为null但是反连接不会忽略null值，而会将所有null值做hash运算，详情见：Oracle反连接HASH JOIN ANTI NA会处理驱动表连接列null值。
select /*+ leading(t) */ count(*) from t where t.col not in (select tt.col from tt); Plan hash value: 1065647028 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.32 | 1577 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.32 | 1577 | | | | |* 2 | HASH JOIN ANTI NA | | 1 | 1000K| 0 |00:00:00.32 | 1577 | 37M| 7549K| 58M (0)| | 3 | TABLE ACCESS FULL| T | 1 | 1000K| 1000K|00:00:00.07 | 1574 | | | | | 4 | TABLE ACCESS FULL| TT | 1 | 1000K| 1 |00:00:00.01 | 3 | | | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T"."COL"="TT"."COL")
这个情景下反而t表所谓驱动表消耗更大速度更慢。
在写Oracle反连接HASH JOIN ANTI NA会处理驱动表连接列null值的时候我曾吐槽过t表做驱动表的时候，不会忽略null值而是连同null都要做hash运算这个动作很不合理，但现在想想其实无可厚非。
觉得不合理是因为觉得null值最终是不符合sql结果会被淘汰的数据，参与hash运算消耗cpu消耗内存多做了无用功。
但是其实由于是表t做驱动表，在这个情景下强制第一个步骤就是对表t做全表扫描，因此是不能自主忽略null值的，原因是，如果not in的表 tt 上应用过滤谓词的结果不包含任何行，那么表t的null值是符合条件的。
09:54:38 ZKM@zkm(37)> set feedback on 09:54:42 ZKM@zkm(37)> select * from (select null colnull from dual) t where t.colnull not in (select * from dual where 1=2); C - 1 row selected. Elapsed: 00:00:00.00
疑问点1
前边说了，NULL-Aware Antijoin则可以按如下方式实现连接子查询展开后的操作：
从tt表检索行，并在tt表上应用过滤器谓词，即where过滤数据，不过我这里没有对tt做where。
如果 tt 上的过滤谓词的结果不包含任何行，则为 t 的每一行返回 t.col，并终止。
如果 tt.col 包含至少一个 NULL 值，则不返回 t 的任何行，并终止。
如果 t.col 与 tt.col 的任何值都不匹配且t.col 为非 NULL，则返回 t.col 以获取 t 中的一行。
按照这个步骤，下边这个sql执行现象就很奇怪（cnull是一张包含大量的全部是null行的表），这是本篇第一个疑问的点。
子查询内容一定返回是0行，在这个结果下，id=2还是会对id=4的结果进行hash运算。根据上边说的“如果 tt 上的过滤谓词的结果不包含任何行，则为 t 的每一行返回 t.col，并终止。”
这里的子查询不包含任何行，你直接返回cnull表的数据就好了为啥还是计算hash值浪费01min08.03s的时间。
select count(*) from cnull t where t.col not in (select * from dual where DUMMY='a' or DUMMY is null); Plan hash value: 1516143242 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:01:17.45 | 437K| | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:17.45 | 437K| | | | |* 2 | HASH JOIN RIGHT ANTI NA| | 1 | 201M| 201M|00:01:08.03 | 437K| 2616K| 2616K| 559K (0)| |* 3 | TABLE ACCESS FULL | DUAL | 1 | 1 | 0 |00:00:00.01 | 2 | | | | | 4 | TABLE ACCESS FULL | CNULL | 1 | 201M| 201M|00:00:15.22 | 437K| | | | ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T"."COL"="DUAL"."DUMMY") 3 - filter(("DUMMY"='a' OR "DUMMY" IS NULL))
接下来简单提下SNA，ANTI SNA表示"single null-aware antijoin."
当确定子查询一定不包含null值的时候，执行路径就会变为
- HASH JOIN ANTI SNA
- HASH JOIN RIGHT ANTI SNA
这种情况下因为主表t可能包含null，值null not in ('a','b','c')实际上等价于null!=a and null!=b and null!=c，这当然不成立。因此主表是否有null值也不能忽略，"single"的意义？呵呵。
SNA基本也适合Cost-Based Optimizer NULL-Aware Antijoin (文档 ID 1082123.1)提到的步骤：
而Single NULL-Aware Antijoin则可以按如下方式实现连接子查询展开后的操作：
从tt表检索行，并在tt表上应用过滤器谓词，即where过滤数据，不过我这里没有对tt做where。
如果 tt 上的过滤谓词的结果不包含任何行，则为 t 的每一行返回 t.col，并终止。
如果 t.col 与 tt.col 的任何值都不匹配且t.col 为非 NULL，则返回 t.col 以获取 t 中的一行。
Optimizer Cost Based Query Transformation (文档 ID 1082127.1)明确了子查询展开中：Null-Aware and Single Null-Aware ANTI JOIN - Note:1082123.1
SNA同样也有疑问点1的问题。
基本SNA跟前边NA的情况都类似，就不赘述了。
疑问点2
到底有没有“NESTED LOOPS ANTI SNA”的路径。防偷。
11g和19c的都死活搞不出来，但是官档里边的例子就有：传送门
select count(*) from t where t.col not in (select /*+ nl_aj */ tt.col from tt); select count(*) from t where t.col not in (select /*+ nl_aj */ tt.col from tt where tt.col is not null); Plan hash value: 2805095820 Plan hash value: 2678459351 ---------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.23 | 3148 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.23 | 3148 | | | | |* 2 | HASH JOIN RIGHT ANTI NA| | 1 | 1000K| 0 |00:00:00.01 | 3 | 1969K| 1969K| 2097K (0)| |* 2 | HASH JOIN RIGHT ANTI SNA| | 1 | 1000K| 0 |00:00:00.23 | 3148 | 2616K| 2616K| 777K (0)| | 3 | TABLE ACCESS FULL | TT | 1 | 1000K| 1 |00:00:00.01 | 3 | | | | |* 3 | TABLE ACCESS FULL | TT | 1 | 1 | 1 |00:00:00.01 | 1574 | | | | | 4 | TABLE ACCESS FULL | T | 0 | 1000K| 0 |00:00:00.01 | 0 | | | | | 4 | TABLE ACCESS FULL | T | 1 | 1000K| 1000K|00:00:00.07 | 1574 | | | | ---------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): Predicate Information (identified by operation id): --------------------------------------------------- --------------------------------------------------- 2 - access("T"."COL"="TT"."COL") 2 - access("T"."COL"="TT"."COL") 3 - filter("TT"."COL" IS NOT NULL)
首先，说下我的观点，我个人觉得NA/SNA是没有相关的NESTED LOOPS的。
为什么？
这之前先看下正常的“NESTED LOOPS ANTI”过程。
在反连接中，“NESTED LOOPS ANTI”方式下驱动表只能是主表做驱动表，比如下边案例中表T做驱动表，而表TT无论如何是无法作为驱动表的。
无论怎么使用如下leading方式，还是qb_name+leading等方式都无法让tt成为驱动表 select count(*) from t where t.col not in select count(*) from t where t.col not in (select /*+ nl_aj */ tt.col from tt where tt.col is not null) and t.col is not null; (select /*+ nl_aj leading(tt) */ tt.col from tt where tt.col is not null) and t.col is not null; Plan hash value: 1951073358 Plan hash value: 1951073358 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 16 | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 16 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 16 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 16 | | 2 | NESTED LOOPS ANTI | | 1 | 1 | 1 |00:00:00.01 | 16 | | 2 | NESTED LOOPS ANTI | | 1 | 1 | 1 |00:00:00.01 | 16 | |* 3 | TABLE ACCESS FULL| T | 1 | 2 | 2 |00:00:00.01 | 6 | |* 3 | TABLE ACCESS FULL| T | 1 | 2 | 2 |00:00:00.01 | 6 | |* 4 | TABLE ACCESS FULL| TT | 2 | 2 | 1 |00:00:00.01 | 10 | |* 4 | TABLE ACCESS FULL| TT | 2 | 2 | 1 |00:00:00.01 | 10 | -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): Predicate Information (identified by operation id): --------------------------------------------------- --------------------------------------------------- 3 - filter("T"."COL" IS NOT NULL) 3 - filter("T"."COL" IS NOT NULL) 4 - filter(("TT"."COL" IS NOT NULL AND "T"."COL"="TT"."COL")) 4 - filter(("TT"."COL" IS NOT NULL AND "T"."COL"="TT"."COL"))
那为什么反连接只能主表做驱动表，以上边SQL的执行计划为例子，id=3是对以对主表T做全表扫描应用谓词条件得到数据作为结果集1，然后将结果集1中依次每行数据根据条件(t.col=tt.col and tt.col is not null)来匹配表TT的数据，当一旦只要在表TT有一行满足(t.col=tt.col and tt.col is not null)的时候，表T的这行数据就是不要的会直接遗弃然后继续对下一行进行同样的流程一直到结果集1全部数据都进行一遍，这是“NESTED LOOPS ANTI”过程的简单描述。
反证法：假设现在表TT作为驱动表，我虚拟构造一个执行计划如下：
先执行id=3对表TT全表扫描并过滤出tt.col is not null的数据作为结果集1，然后将结果集1中依次每行数据根据条件(tt.col=t.col and t.col is not null)来匹配表T的数据，当有满足条件条件(tt.col=t.col and t.col is not null)的时候，证明匹配的表T（注意不是表TT）的那一批满足条件的数据都是不要的，那除了这一批数据以外表T剩下的其他数据呢？你无法判断剩余的其他数据是否会和结果集1还没进行匹配的行能否匹配的上，因此表TT作为驱动表的算法是无法进行下去的。
虚假的执行计划： -------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 16 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 16 | | 2 | NESTED LOOPS RIGHT ANTI | | 1 | 1 | 1 |00:00:00.01 | 16 | |* 3 | TABLE ACCESS FULL | TT | 1 | 2 | 2 |00:00:00.01 | 6 | |* 4 | TABLE ACCESS FULL | T | 2 | 2 | 1 |00:00:00.01 | 10 | --------------------------------------------------------------------------------------------
当not in主表（t）和次表（tt）连接列可能为空时，执行SQL：select count(*) from t where t.col not in (select tt.col from tt)，此时可以进入NULL-Aware Antijoin。
上边说了，如果是“NESTED LOOPS ANTI”，那么只能是主表T做驱动表，这个其实和前边mos文档的流程是冲突的：
NULL-Aware Antijoin则可以按如下方式实现连接子查询展开后的操作：（按照mos文档说的这个步骤，必须首先先访问次表TT，那么表TT就只能作为驱动表）
从tt表检索行，并在tt表上应用过滤器谓词，即where过滤数据，不过我这里没有对tt做where。
如果 tt 上的过滤谓词的结果不包含任何行，则为 t 的每一行返回 t.col，并终止。
如果 tt.col 包含至少一个 NULL 值，则不返回 t 的任何行，并终止。
如果 t.col 与 tt.col 的任何值都不匹配且t.col 为非 NULL，则返回 t.col 以获取 t 中的一行。
什么，你说“HASH JOIN ANTI NA”（主表T做驱动表）也其实不符合流程？是，所以主表T驱动表的时候会计算null的hash运算值，而且也已经跟mos文档的流程不一致了。
假设现在有“NESTED LOOPS ANTI SNA”或者“NESTED LOOPS ANTI NA”，再次虚构一个执行计划如下：
你用驱动表T去驱动表TT数据的时候，必须使用“T.COL=TT.COL”这个条件进行关联，这个时候=判断已经自动忽略过滤了两边的null值【注1】，而NA/SNA本质就是处理可能出现null值的情况，这个互相矛盾。
而且表T的null是不能够被忽略的，前边提到当not in的子查询一条数据都没有，那么表T的null值是符合条件的。
虚假的执行计划： select count(*) from t where t.col not in (select tt.col from tt); ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 10 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 10 | | 2 | NESTED LOOPS ANTI NA | | 1 | 2 | 0 |00:00:00.01 | 10 | | 3 | TABLE ACCESS FULL | T | 1 | 3 | 3 |00:00:00.01 | 6 | |* 4 | TABLE ACCESS FULL | TT | 1 | 3 | 3 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."COL"="TT"."COL")
【注1】
"HASH JOIN RIGHT ANTI NA/SNA"和“HASH JOIN ANTI NA/SNA”也有“T.COL=TT.COL”，但是并不会忽略null值，因为HASH JOIN对null做了hash运算，null已非hash(null)。
