Oracle in與exists的選擇

業務問題大概可以這樣描述，一個父表，一個子表，查詢的結果是找到子表中沒有使用父表id的記錄，這種情況估計很多系統都會牽涉得到。讓我們來舉一個例子：

　　表一： 父表 parent

             1、ID        varchar2(10)         主鍵

             2、NAME VARCHAR2(100) 名稱

　　表二： 子表 childen

1、ID       VARCHAR2(10)     主鍵

2、PID    VARCHAR2(10)     主表的表示

3、NAME   VARCHAR2(100) 名稱

　　父表格儲存體父親，子表格儲存體孩子，然後通過pid和父表關聯，查詢需要的結果是找到尚未有孩子的父親。

　　我們來看一下查詢語句的寫法：

　　select * from parent where id not in (select pid　from childen)

　　這種標準的寫法在子表存在50萬條的記錄的時候，查詢時間超過了10秒，遠遠大於原來的sql server伺服器的一秒。我在解決的時候想到了一個方法：

　　select * from parent where id in

　　( select id from parent minus select pid　from childen )

　　正常理解下，這個語句應該更加費時，但是事實完全出乎意料，這條語句不僅僅在子表存在大量記錄的情況下速度良好，在子表少量資料的情況下速度也非常的好，基本在1秒內完成。

select * from A
where id in(select id from B)

以上查詢使用了in語句,in()只執行一次,它查出B表中的所有id欄位並緩衝起來.之後,檢查A表的id是否與B表中的id相等,如果相等則將A表的記錄加入結果集中,直到遍曆完A表的所有記錄.
它的查詢過程類似於以下過程

List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);

for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;

可以看出,當B表資料較大時不適合使用in(),因為它會B表資料全部遍曆一次.
如:A表有10000條記錄,B表有1000000條記錄,那麼最多有可能遍曆10000*1000000次,效率很差.
再如:A表有10000條記錄,B表有100條記錄,那麼最多有可能遍曆10000*100次,遍曆次數大大減少,效率大大提升.

結論:in()適合B表比A表資料小的情況

select a.* from A a
where exists(select 1 from B b where a.id=b.id)

以上查詢使用了exists語句,exists()會執行A.length次,它並不緩衝exists()結果集,因為exists()結果集的內容並不重要,重要的是結果集中是否有記錄,如果有則返回true,沒有則返回false.
它的查詢過程類似於以下過程

List resultSet=[];
Array A=(select * from A)

for(int i=0;i<A.length;i++) {
   if(exists(A[i].id) {    //執行select 1 from B b where b.id=a.id是否有記錄返回
       resultSet.add(A[i]);
   }
}
return resultSet;

當B表比A表資料大時適合使用exists(),因為它沒有那麼遍曆操作,只需要再執行一次查詢就行.
如:A表有10000條記錄,B表有1000000條記錄,那麼exists()會執行10000次去判斷A表中的id是否與B表中的id相等.
如:A表有10000條記錄,B表有100000000條記錄,那麼exists()還是執行10000次,因為它只執行A.length次,可見B表資料越多,越適合exists()發揮效果.
再如:A表有10000條記錄,B表有100條記錄,那麼exists()還是執行10000次,還不如使用in()遍曆10000*100次,因為in()是在記憶體裡遍曆比較,而exists()需要查詢資料庫,我們都知道查詢資料庫所消耗的效能更高,而記憶體比較很快.

結論:exists()適合B表比A表資料大的情況

當A表資料與B表資料一樣大時,in與exists效率差不多,可任選一個使用.