ORACLE NOT IN:NULL AWARE ANTI JOIN注意点及其改写

建表语句如下：

drop table a;
drop table b;
create table a as select * from dba_objects;
create table b as select * from dba_objects;
delete from b where rownum<100;
insert into a select * from a;
insert into a select * from a;
insert into a select * from a;
insert into b select * from b;
insert into b select * from b;
insert into b select * from b;
commit;
create index idx_a on a(object_name);
create index idx_b on b(object_name);

收集统计信息:

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'a',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'b',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);

统计信息如下：

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
A                                  613150       61315 2023-08-18 11:09:32


TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
B                                  612240       61224 2023-08-18 11:09:33


数据量如下：

select count(*) from a;
  COUNT(*)
----------
    614696


select count(*) from b;
  COUNT(*)
----------
    613912



SQL如下所示：

select * from a where a.object_name not in (select b.object_name from b);


运行需要10分钟以上，走FILTER，有索引也用不上，因为NOT IN条件转为LNNVL("B"."OBJECT_NAME"<>:B1，执行计划如下：

分析和优化：
主要是a,b的object_name允许null,所以not in不能查询转换成semi join

desc a
 Name                      Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 OWNER                              VARCHAR2(30)
 OBJECT_NAME  VARCHAR2(128)
 。。。
desc b
 Name                      Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 OWNER                              VARCHAR2(30)
 OBJECT_NAME  VARCHAR2(128)
 。。。

11g有null aware anti join,查看参数：
NAME                                     VALUE
---------------------------------------- ----------------------------------------
_optimizer_null_aware_antijoin           FALSE


参数关闭所以不行，开启后发现还是走FILTER:
alter session set "_optimizer_null_aware_antijoin"=true;



关闭，打开即可：进一步分析，可以用sqlt xplore分析，可能是BUG或其他参数影响，{BANNED}{BANNED}最佳佳终发现是_optimizer_squ_bottomup关闭，打开即可：

alter session set "_optimizer_squ_bottomup"= true;


打开后走HASH JOIN RIGHT ANTI NA，只需要0.63s,返回结果792行：



对于not in的null aware anti join转换和下列参数有关：


NAME                                     VALUE
---------------------------------------- ----------------------------------------
_optimizer_cost_based_transformation     LINEAR
_optimizer_squ_bottomup                  TRUE
_optimizer_null_aware_antijoin           TRUE


以上参数必须是默认值，不能关闭。


其他改写优化：可以改写为NOT EXISTS或A,B都对object_name加is not null或外连接+inner table.notnull col is null，或者增加约束也可以，“”注意如果的确子查询存在null,则语句没有结果，不能用上面的改写“。

alter session set "_optimizer_null_aware_antijoin"=false;


--增加xx is not null
select * from a where a.object_name not in (select b.object_name from b where b.object_name is not null) and a.object_name is not null;

执行计划走HASH JOIN RIGHT ANTI：



改为 not exists，同样走HASH JOIN RIGHT ANTI：
select * from a where not exists(select 1 from b where a.object_name = b.object_name);




--改写为外连接

select * from a left join  b on a.object_name = b.object_name where b.object_id is null;

走HASH JOIN RIGHT OUTER，只不过HASH JOIN完还需要FILTER：b.object_id is null，所以效率差点，如下所示：

        
MySQL里的not in子查询也是个大问题，如果关联列没有not null约束，增加is not null条件并不能转为anti join（也即只能走SUQUERY，也就是unnest,树形结构执行计划就是走FILTER）,因为MySQL没有实现NULL AWARE ANTI JOIN，在MySQL里可以增加约束，改写为not exists,外连接+is null等实现。