NOT IN ,NOT EXISTS 區別 11G改變

先看兩個查詢。
select  count(*) from wxh_tbd where object_id not in (1,2,3);

  COUNT(*)
----------
     20204
select count(*) from wxh_tbd where object_id not in (1,2,3,null);

  COUNT(*)
----------
         0

很多人會覺得奇怪，怎麼多了一個NULL值，第二個查詢就沒返回了？

第二個查詢可以拆分為：
select count(*) from wxh_tbd where object_id!=1 and object_id!=2 and object_id!=3 and object_id!=null;
not in拆分後，是and的關係，只要一個不為true,那麼整條表示式都是false,返回為空。

因此對於類似這樣的查詢
select object_id from wxh_tbd where object_id not in ( select object_id from wxh_tbd1);

只要子查詢返回的結果集裡有空值，那麼整個結果集都是空。因為每個wxh_tbd.OBJECT_ID都是要跟NULL值做比較的，做比較的結果是FALSE.

 

無論如何in不存在這樣的問題。
select count(*) from wxh_tbd where object_id  in (1,2,3,null);  
可以拆分成：
select count(*) from wxh_tbd where object_id=1 or object_id=2 or object_id=3 or object_id=null;
跟NOT IN不同，IN拆分後是OR的關係，只要有一個為true，那麼表示式就是true,就可以返回結果。

11G以前not in經常會導致問題。拿上面的查詢來說：
select object_id from wxh_tbd where object_id not in ( select object_id from wxh_tbd1);
如果wxh_tbd1.object_id,wxh_tbd.object_id上沒有非空約束，那麼查詢計劃會走FILTER，而且往往wxh_tbd1上是全表掃描。走不上NL,HASH.之所以走全表掃描可能基於一個簡單的原因，NULL值不包含在索引裡。

11G以後呢？
explain plan for
select object_id from wxh_tbd where object_id not in ( select object_id from wxh_tbd1);

Explained.

@display

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4021671869

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          | 20414 |   199K|   131   (1)| 00:00:02 |
|*  1 |  HASH JOIN ANTI NA |          | 20414 |   199K|   131   (1)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| WXH_TBD  | 20446 |    99K|    65   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| WXH_TBD1 | 20411 |    99K|    65   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"="OBJECT_ID")
其實11G的這種最佳化在我看來有點遲了，早就應該可以實現，無非是加一些IF ELSE判斷。掃描WXH_TBD表，扔掉為空的記錄，BUILD出HASH表。跟WXH_TBD1表做HASH，如果WXH_TBD1表裡存在NULL值，立馬終止查詢，返回結果空。

11G即使表上都沒NOT NULL約束，也可以走jion了,執行計劃出現了 NA關鍵字，目前只發現可以走hash join.

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | WXH_TBD     |       |       |       |
|*  3 |   TABLE ACCESS FULL  | WXH_TBD1    |       |       |       |
--------------------------------------------------------------------
11G以前無論如何不能走JION，只能走FILTER.

再看看NOT EXISTS對於空值的返回順序：

select object_id from wxh_tbd where not exists ( select 1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);
如果走hash,WXH_TBD為build table最先返回的是空值。道理其實很簡單，HASH 表裡空值不能被BUILD進去，可以掃描到空值就直接返回結果。如果wxh_tbd1為build table ,空值是混合在記錄裡的。

select  object_id from wxh_tbd where not exists ( select /*+ swap_join_inputs(wxh_tbd1)  */1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);

如果走nest loop ,null值是混合在記錄裡的。

對於not in，如果在wxh_tbd表裡存在空值，wxh_tbd1表裡沒空值，那麼整個查詢會返回符合條件的結果集，但是空值不會作為結果返回。
對於not exists,如果在wxh_tbd表裡存在空值，那麼整個查詢會返回符合條件的結果集，而且空值會作為結果返回。

理論上，IN 與EXISTS是完全等價的，但是各個ORACLE版本可能執行計劃存在差異，（10G以後應該無任何差異了）。
NOT IN 與NOT EXISTS的差異根據有NULL的不同而不同。如果作為條件判斷的欄位都存在NOT NULL約束，那麼這種差異也就不存在了。