EXISTS、IN、NOT EXISTS、NOT IN用法區別

shwenwen發表於2007-10-08
EXISTS、IN、NOT EXISTS、NOT IN用法區別[@more@]
EXISTS、IN、NOT EXISTS、NOT IN:
in適合內外表都很大的情況,exists適合外表結果集很小的情況。

exists 和 in 使用一例
===========================================================
今天市場報告有個sql及慢,執行需要20多分鐘,如下:
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where exists(
select 1
from (
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = 'sssssssssssssssss'
) a
where a.decl_no = cd.decl_no
and a.goods_no = cd.goods_no
)
上面涉及的3個表的記錄數都不小,均在百萬左右。根據這種情況,我想到了前不久看的tom的一篇文章,說的是exists和in的區別,
in 是把外表和那表作hash join,而exists是對外表作loop,每次loop再對那表進行查詢。
這樣的話,in適合內外表都很大的情況,exists適合外表結果集很小的情況。

而我目前的情況適合用in來作查詢,於是我改寫了sql,如下:
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where (decl_no,goods_no) in
(
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = ‘ssssssssssss’
)

讓市場人員測試,結果執行時間在1分鐘內。問題解決了,看來exists和in確實是要根據表的資料量來決定使用。

請注意not in 邏輯上不完全等同於not exists,如果你誤用了not in,小心你的程式存在致命的BUG:

請看下面的例子:
create table t1 (c1 number,c2 number);
create table t2 (c1 number,c2 number);

insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
insert into t2 values (1,null);

select * from t1 where c2 not in (select c2 from t2);
no rows found
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
c1 c2
1 3

正如所看到的,not in 出現了不期望的結果集,存在邏輯錯誤。如果看一下上述兩個select語句的執行計劃,也會不同。後者使用了hash_aj。
因此,請儘量不要使用not in(它會呼叫子查詢),而儘量使用not exists(它會呼叫關聯子查詢)。如果子查詢中返回的任意一條記錄含有空值,則查詢將不返回任何記錄,正如上面例子所示。
除非子查詢欄位有非空限制,這時可以使用not in ,並且也可以透過提示讓它使用hasg_aj或merge_aj連線。

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

相關文章