Oracle反连接HASH JOIN ANTI NA会处理驱动表连接列null值
这个现象和Oracle内连接HASH JOIN/半连接HASH JOIN SEMI不处理驱动表连接列null值相反。
反连接中无论一下哪个结论都一样：
- HASH JOIN ANTI NA
- HASH JOIN ANTI SNA
- HASH JOIN RIGHT ANTI NA
- HASH JOIN RIGHT ANTI SNA
测试表t,tt的信息如下：其中t表全是null值。
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; 15:17:43 ZKM@zkm(37)> select col,count(*) from tt group by col;
COL COUNT(*) COL COUNT(*)
------ ---------- ------ ----------
1000000 1
a 1
Elapsed: 00:00:00.16
Elapsed: 00:00:00.00
现有sql和执行情况如下：
其中，id=2花费的时间达到25s（33-7-1），如果是Oracle内连接HASH JOIN/半连接HASH JOIN SEMI不处理驱动表连接列null值中，会忽略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.33 | 1578 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.33 | 1578 | | | |
|* 2 | HASH JOIN ANTI NA | | 1 | 201M| 0 |00:00:00.33 | 1578 | 37M| 7549K| 58M (0)|
| 3 | TABLE ACCESS FULL| T | 1 | 201M| 1000K|00:00:00.07 | 1574 | | | |
| 4 | TABLE ACCESS FULL| TT | 1 | 2 | 1 |00:00:00.01 | 4 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."COL"="TT"."COL")
创建索引，还是一样。
create index idx_col on t(col);
insert多15000000多行null值数据，如下：
16:43:47 ZKM@zkm(37)> select col,count(*) from t group by col;
COL COUNT(*)
------------------------------------------------------------ ----------
16000000
Elapsed: 00:00:02.62
再次执行sql语句：防偷
时间和消耗大大增加，说明在反连接的情况下，hash join无法忽略null值，还是会对null进行hash值计算，其实这很不合理。
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 | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:06.05 | 24392 | 14229 | 14229 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:06.05 | 24392 | 14229 | 14229 | | | | |
|* 2 | HASH JOIN ANTI NA | | 1 | 201M| 0 |00:00:06.05 | 24392 | 14229 | 14229 | 542M| 29M| 892M (0)| 115K|
| 3 | TABLE ACCESS FULL| T | 1 | 201M| 16M|00:00:01.18 | 24388 | 0 | 0 | | | | |
| 4 | TABLE ACCESS FULL| TT | 1 | 2 | 1 |00:00:00.01 | 4 | 0 | 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."COL"="TT"."COL")
标签：00,HASH,NA,col,COL,JOIN,null,连接 From： https://www.cnblogs.com/PiscesCanon/p/17480749.html