【oracle調優】in和exists____oracle

In和exists

         相信很多朋友跟我一樣，都曾經為這兩個關於誰更快的問題糾結過，也曾腦子裡不知為什麼的記下了一些標準，比如在子表小的情況下in更快，在外表更小的情況下要用exists等。

         但實際你的11g中做下測試，你會感到很奇怪。

         對於兩個子表大的情況和外表更大的情況，in和exists會得到相同的執行計畫。

在很早的10g，oracle好像就最佳化了in和exists的區別，兩者通常不會再有什麼效能方向的差別，因為oracle最佳化器會自動根據兩個表的大小，將in和exists的語句重寫為合適的連接。

所以結論就是，在現在的oracle版本中，in和exists在效能上已經基本等效了，不需要在考慮要使用其中一種替換另外一種了。 SEMI JOIN和ANTI JOIN

         在查看in和exists語句的執行計畫時，可能經常會看到上面的這兩個連接。

         SEMIJOIN半連接

         ANTIJOIN反連接

         半連接的概念：常用於in，exists等產生的執行計畫，所以拿in語句來舉例。比如

         Select  a.* from a where a.id in (1,3,5,7)

         假設我們上一條查詢走的是以a表為驅動的nestloop，則當我們從a表裡取出一條記錄，然後跟（1,3,5,7）去對比相等。若該記錄值為1，則該記錄在第一次對比就得到true的結果，則該記錄就可以直接返回，而不需要再跟（3,5,7）去做一一的對比。這就相當於只跟列表中的部分值做對比就可以確定結果。所以叫半連接。

         反連接的概念：常用語not in，not exists等產生的執行計畫。還拿notin舉例

         Select  a.* from a where a.id not in (1,3,5,7)

         當我們從a中取出一條記錄，該記錄值為1，當我們做第一次對比，發現結果為false，則這條記錄直接可以判定是不滿足條件的，可以直接捨棄，繼續a表的下一條資料對比了。這樣的話也跟半連接操作一樣，也不需要產生兩表記錄數乘積的比較運算，因此也能節省成本。這就是反連接。

         需要強調的是，半連接和反連接是一種邏輯的連接方式，跟等值連接，交叉連接等一樣，它並不是三大實現方式（nest loop,sort merge,hash）之外的另外一種實現。所以半連接和反連接也是各自有三種實現方式的，比如我可以是hash join anti（雜湊反），nest loop anti(嵌套迴圈反)等。 Not in和not exists

         Notin和not exists在邏輯上並不是完全等效的。所以，在更改寫法之前一定要驗證邏輯上是否等效，是否可以改寫。

         Notin和not exists的不等效主要是因為對null的處理不同。

         首先必須明白，在oracle資料庫裡，null值不能進行各種算術運算，包括=和<>。判斷是否為空白的話只能使用IS NULL和IS NOT NULL。

         Select* from tabA where id = null;

         Select* from tabA where id <> null;

         以上兩條sql會沒有任何返回結果，即使id列有空值。這就是因為null值無法進行等或不等的判斷。

         然後就到了not in和not exists的區別了。

         引用一張http://blog.csdn.net/elvis_dataguru/article/details/8426886的圖吧。

         通過這張圖能很好的理解。

         當使用not in時，無論如何不會返回空值。當子查詢中有null值時，沒有任何返回結果。

         而not exists因為=判斷是在子查詢裡進行，所以外表的null值能被返回。