Oracle中的in和exists區別

在SQL查詢語句中，經常會很多人對in和exists的查詢效率進行疑惑，很多人都認為exists查詢速度要比in快，其實這個說話不絕對，exists和in都有它們適合的場合，不然在SQL查詢標準中，也不會一直不遺餘力的進行支援。

先說in，通常情況下我們即認為是先將in子句裡面的內容查詢出來，然後對In的查詢結果進行合并，再根據查詢結果對主SQL進行一個個的查詢，即相當於以下轉換。
　　select * from T1 where x in (select y from T2);　　
　　可以轉換成如下　　
　　select * from T1,(select distinct y from T2) T2　　
　　where T1.x=T2.y;

再說exists，exists即先從主SQL中找到每一條合適的記錄，然後將結果放到子SQL中與之匹配，即　　
　　select * from T1 where exists (select NULL from T2 where T2.y=T1.x);　　
　　可以轉換成
　　for cursor1 in (select * from T1)　　
　　loop　　
　　if (exists (select NULL from T2 where T2.y=cursor1.x))　　
　　then　　
　　返回記錄;　　
　　end if;　　
end loop;

 

這樣子，即我們很容易會認為在子SQL資料量比較大的時候，exists效率會高於in，而只有在in的結果集非常小的時候，in的效率才能比exists好，　這樣是否就正確了呢？通過對很多的SQL進行分析，會發現這個準則也不絕對，特別是10g，11g之後的資料庫，in和exist很多時候效果都差不多，這又是為什麼呢？

再深入分析，Oracle最佳化器規則中有三種分析規則，以前主要是基於規則的最佳化器，即通過預先定義一系列的優先順序順序，比如唯一索引優先於普通索引，又或者是等於索引優先於大於索引，這樣即按規則的優先順序去執行SQL，所以我們就有了在子句結果集很小的時候，in查詢速度會快於exists，反之則exists速度會更快的結論，在oracle 9i中，預設提供是基於選擇的最佳化器，即當有分析資料時，採用基於成本的最佳化方式，沒有則仍採用基於規則的查詢方式，這樣最佳化模式下基本等同於基於規則或者基於成本。在10g之後的版本，預設都是以基於成本的方式進行，這時候，oracle會先找出可能的執行方式，然後計算出每個執行計畫的成本，再選擇以較低成本的方式進行計算，這樣子在對in和exists的分析中，這兩種寫法會相互轉換，那個統計的成本資訊低則會選擇那種方式。當然由於oracle的成本資訊並不是全量統計得出來的結果，也會有一定的誤差，再統計資訊是需要人工（或定時）去執行統計的，如果操作大量資料後，沒有進行統計，偏差也會很大。

這樣子的分析是否準確了呢？in和exists又是不是只有這兩種查詢方式呢？在基於成本的最佳化方式中，又如何對in和exists進行最佳化？SQL的最佳化又是否與具體資料有關？

閱讀延伸：關於Oracle中in和exists的區別：