oracle not in:null aware anti join注意点及其改写-凯发app官方网站

建表语句如下：

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等实现。