_optimizer_null_aware_antijoin 参数是Oracle 11g引入的新参数，它用于解决在反连接（Anti-Join）时，关联列上存在空值（NULL）或关联列无非空约束的问题 默认情况下，该参数的值为true，即开启状态。 我们用一个例子简单看一下这个参数的效果 设置关闭该参数： SQL> alter system set "_optimizer_null_aware_antijoin"=false; System altered. 新建两张测试表： create table t1 as select * from dba_objects; create table t2 as select * from dba_tables; 关闭情况下，使用not in子句查询时使用的filter SQL> select * from t1 where object_name not in (select table_name from t2); 83339 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 895956251 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 87226 | 17M| 114K (1)| 00:22:51 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T1 | 87258 | 17M| 345 (1)| 00:00:05 | |* 3 | TABLE ACCESS FULL| T2 | 2453 | 41701 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE LNNVL("TABLE_NAME"<>:B1))) 3 - filter(LNNVL("TABLE_NAME"<>:B1)) 开启该参数 SQL> alter system set "_optimizer_null_aware_antijoin"=true; System altered. 可以看到查询效率得到很大的提升： SQL> select * from t1 where object_name not in (select table_name from t2); 83339 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1521920066 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 87258 | 18M| 377 (1)| 00:00:05 | |* 1 | HASH JOIN RIGHT ANTI SNA| | 87258 | 18M| 377 (1)| 00:00:05 | | 2 | TABLE ACCESS FULL | T2 | 2582 | 43894 | 31 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 87258 | 17M| 345 (1)| 00:00:05 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_NAME"="TABLE_NAME") 我们看看把not in换成not exists的SQL执行计划： SQL> select * from t1 where not exists (select 1 from t2 where t2.table_name=t1.object_name); 83339 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 629543484 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 87258 | 18M| 377 (1)| 00:00:05 | |* 1 | HASH JOIN RIGHT ANTI| | 87258 | 18M| 377 (1)| 00:00:05 | | 2 | TABLE ACCESS FULL | T2 | 2582 | 43894 | 31 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 87258 | 17M| 345 (1)| 00:00:05 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T2"."TABLE_NAME"="T1"."OBJECT_NAME") Note ----- - dynamic sampling used for this statement (level=2) _optimizer_null_aware_antijoin参数能优化查询效率，但是查看mos，开启该隐含参数会有很多的bug。可能存在不稳定，因此通常会建议把该参数关闭。 alter system set "_optimizer_null_aware_antijoin"=false; 该参数为动态参数。