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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-2121249/，如需轉載，請註明出處，否則將追究法律責任。