oracle中not in和not exsist詳解

bitifi發表於2015-09-22
以下內容轉至自:http://blog.sina.com.cn/s/blog_6ff05a2c0100tref.html

在網上搜了下關於oraclenot existsnot in效能的比較,發現沒有描述的太全面的,可能是問題太簡單了,達人們都不屑於解釋吧。於是自己花了點時間,試圖把這個問題簡單描述清楚,其實歸根結底一句話:not in效能並不比not exists差,關鍵看你用的是否正確。

 

我先建兩個示範表,便於說明:

create table  ljn_test1 (col number);

create table  ljn_test2 (col number);

然後插入一些資料:

insert into ljn_test1

select level from dual connect by level <=30000;

insert into ljn_test2

select level+1 from dual connect by level <=30000;

commit;

然後來分別看一下使用not existsnot in的效能差異:

select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);

 

       COL

----------

         1

 

Elapsed: 00:00:00.06

select * from ljn_test1 where col not in (select col from ljn_test2);

 

       COL

----------

         1

 

Elapsed: 00:00:21.28

可以看到,使用not exists需要0.06秒,而使用not in需要21秒,差了3個數量級!為什麼呢?其實答案很簡答,以上兩個SQL其實並不是等價的。

我把以上兩個表的資料清除掉,重新插入資料:

truncate table ljn_test1;

truncate table ljn_test2;

insert into ljn_test1 values(1);

insert into ljn_test1 values(2);

insert into ljn_test1 values(3);

insert into ljn_test2 values(2);

insert into ljn_test2 values(null);

commit;

然後再次執行兩個SQL

select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);

 

       COL

----------

         3

         1

 

select * from ljn_test1 where col not in (select col from ljn_test2);

 

no rows selected

這回not in的原形暴露了,竟然得到的是空集。來仔細分解一下原因:

A.  select * from ljn_test1 where col not in (select col from ljn_test2);

A在這個例子中可以轉化為下面的B

B.  select * from ljn_test1 where col not in (2,null);

B可以進一步轉化為下面的C

C.  select * from ljn_test1 where col <> 2 and col <> null;

因為col <> null是一個永假式,所以最終查出的結果肯定也就是空了。

由此可以得出結論:只要not in的子查詢中包含空值,那麼最終的結果就為空!

not exists語句不會出現這種情況,因為not exists子句中寫的是ljn_test1ljn_test2的關聯,null是不參與等值關聯的,所以ljn_test2col存在空值對最終的查詢結果沒有任何影響。

我在這裡暫且把ljn_test1叫做外表,ljn_test2叫做內表。

只要稍做歸納,就可以得到更詳細的結論:

1、對於not exists查詢,內表存在空值對查詢結果沒有影響;對於not in查詢,內表存在空值將導致最終的查詢結果為空。

2、對於not exists查詢,外表存在空值,存在空值的那條記錄最終會輸出;對於not in查詢,外表存在空值,存在空值的那條記錄最終將被過濾,其他資料不受影響。

 

講到這裡,我就可以開始解釋為什麼上面的not in語句比not exists語句效率差這麼多了。

not exists語句很顯然就是一個簡單的兩表關聯,內表與外表中存在空值本身就不參與關聯,在CBO(基於成本的最佳化器)中常用的執行計劃是hash join,所以它的效率完全沒有問題,看一下它的執行計劃:

set autot on;

select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);

 

       COL

----------

         3

         1

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 385135874

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     3 |    78 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN ANTI    |           |     3 |    78 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| LJN_TEST1 |     3 |    39 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| LJN_TEST2 |     2 |    26 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("LJN_TEST1"."COL"="LJN_TEST2"."COL")

 

這個執行計劃很清晰,沒有什麼需要解釋的,再看一下not in:

 

select * from ljn_test1 where col not in (select col from ljn_test2);

 

no rows selected

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3267714838

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |    13 |     5   (0)| 00:00:01 |

|*  1 |  FILTER            |           |       |       |            |          |

|   2 |   TABLE ACCESS FULL| LJN_TEST1 |     3 |    39 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| LJN_TEST2 |     2 |    26 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter( NOT EXISTS (SELECT 0 FROM "LJN_TEST2" "LJN_TEST2"

              WHERE LNNVL("COL"<>:B1)))

   3 - filter(LNNVL("COL"<>:B1))

 

可以看到關聯謂詞是filter,它類似於兩表關聯中的nested loop,也就是跑兩層迴圈,可見它的效率有多差。為什麼not in不能使用hash join作為執行計劃呢?正如上面解釋的,因為內表或外表中存在空值對最終結果產生的影響是hash join無法實現的,因為hash join不支援把空值放到hash桶中,所以它沒辦法處理外表和內表中存在的空值,效率與正確性放在一起時,肯定是要選擇正確性,所以oracle必須放棄效率,保證正確性,採用filter謂詞。

 

這個執行計劃中我們還有感興趣的東西,那就是:LNNVL("COL"<>:B1)關於LNNVL的解釋可以參見官方文件:

它在這裡的作用很巧妙,oracle知道使用filter效能很差,所以它在掃描內表ljn_test2時,會使用LNNVL來檢查ljn_test2.col是否存在null值,只要掃描到null值,就可以斷定最終的結果為空值,也就沒有了繼續執行的意義,所以oracle可以馬上終止執行,在某種意義上它彌補了filter較差的效能。

我用例子來證明這一點,首先先造一些資料:

truncate table ljn_test1;

truncate table ljn_test2;

insert into ljn_test1

select level from dual connect by level <=30000;

insert into ljn_test2

select level+1 from dual connect by level <=30000;

commit;

然後我為了讓oracle儘快掃描到ljn_test2.colnull的那條記錄,我要先找到實體地址最小的那條記錄,因為通常情況全表掃描會先掃描實體地址最小的那條記錄:

select col from ljn_test2 where rowid=(select min(rowid) from ljn_test2);

 

       COL

----------

      1982

然後我把這條記錄更新為空:

update ljn_test2 set col = null where col=1982;

commit;

然後再來看一下not in的查詢效率:

select * from ljn_test1 where col not in (select col from ljn_test2);

 

no rows selected

 

Elapsed: 00:00:00.17

 

看到這個結果後我很爽,它和之前查詢需要用時21秒有很大的差別!

當然,我們不能總是指望oracle掃描表時總是最先找到null值,看下面的例子:

update ljn_test2 set col = 1982 where col is null;

select col from ljn_test2 where rowid=(select max(rowid) from ljn_test2);

 

       COL

----------

     30001

update ljn_test2 set col = null where col=30001;

commit;

再看一下not in的查詢效率:

select * from ljn_test1 where col not in (select col from ljn_test2);

 

       COL

----------

         1

 

Elapsed: 00:00:21.11

這一下not in再一次原形畢露了!

機會主義不行,更杯具的是如果內表中沒有空值,那LNNVL最佳化就永遠起不到作用,相反它還會增大開銷!

其實只要找到原因,問題很好解決,不就是空值在作怪嘛!在正常的邏輯下使用者本來就是想得到和not exists等價的查詢結果,所以只要讓oracle知道我們不需要空值參與進來就可以了。

第一種解決方案:

將內表與外表的關聯欄位設定為非空的

alter table ljn_test1 modify col not null;

alter table ljn_test2 modify col not null;

好了,再看一下執行計劃:

set autot on;

select * from ljn_test1 where col not in (select col from ljn_test2);

 

       COL

----------

         1

 

Elapsed: 00:00:00.07

 

Execution Plan

----------------------------------------------------------

Plan hash value: 385135874

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |    26 |    28   (8)| 00:00:01 |

|*  1 |  HASH JOIN ANTI    |           |     1 |    26 |    28   (8)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| LJN_TEST1 | 30000 |   380K|    13   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| LJN_TEST2 | 30000 |   380K|    13   (0)| 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("COL"="COL")

 

很好!這回oracle已經知道使用hash join了!不過有時候表中需要儲存空值,這時候就不能在表結構上指定非空了,那也同樣簡單:

第二種解決方案:

查詢時在內表與外表中過濾空值。

先把表結構恢復為允許空值的:

alter table ljn_test1 modify col null;

alter table ljn_test2 modify col null;

然後改造查詢:

select * from ljn_test1 where col is not null and col not in (select col from ljn_test2 where col is not null);

 

       COL

----------

         1

 

Elapsed: 00:00:00.07

 

Execution Plan

----------------------------------------------------------

Plan hash value: 385135874

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |    26 |    28   (8)| 00:00:01 |

|*  1 |  HASH JOIN ANTI    |           |     1 |    26 |    28   (8)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| LJN_TEST1 | 30000 |   380K|    13   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| LJN_TEST2 | 30000 |   380K|    13   (0)| 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("COL"="COL")

   2 - filter("COL" IS NOT NULL)

   3 - filter("COL" IS NOT NULL)

 

OK! hash join出來了!我想我關於not existsnot in之間的比較也該結束了。


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

相關文章