Oracle 11g中的_optimizer_null_aware_antijoin隐含参数

_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;
该参数为动态参数。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23850820/viewspace-2121249/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23850820/viewspace-2121249/