oracle中的exists和in

有時候會將一列和一系列值相比較。最簡單的辦法就是在 where 子句中使用子查詢。在
where子句中可以使用兩種格式的子查詢。 
第一種格式是使用IN操作符： 
... where column in(select * from ... where ...);  
第二種格式是使用EXIST操作符：
... where exists (select 'X' from ...where ...);  
 
我相信絕大多數人會使用第一種格式，因為它比較容易編寫，而實際上第二種格式要遠比第
一種格式的效率高。在 Oracle中可以幾乎將所有的 IN操作符子查詢改寫為使用 EXISTS的子查
詢。

第二種格式中， 子查詢以‘ select 'X' 開始。運用 EXISTS 子句不管子查詢從表中抽取什麼數
據它只查看 where 子句。 這樣最佳化器就不必遍曆整個表而僅根據索引就可完成工作（這裡假定在
where 語句中使用的列存在索引）。相對於 IN 子句來說， EXISTS 使用相連子查詢，構造起來要
比 IN 子查詢困難一些。

通過使用 EXIST ， Oracle 系統會首先檢查主查詢，然後運行子查詢直到它找到第一個匹配
項，這就節省了時間。 Oracle 系統在執行 IN 子查詢時，首先執行子查詢，並將獲得的結果清單
存放在在一個加了索引的暫存資料表中。在執行子查詢之前， 系統先將主查詢掛起，待子查詢執行完
畢，存放在暫存資料表中以後再執行主查詢。這也就是使用 EXISTS 比使用 IN 通常查詢速度快的原
因。

同時應儘可能使用 NOT EXISTS 來代替 NOT IN ，儘管二者都使用了 NOT （不能使用索引
而降低速度）， NOT EXISTS 要比 NOT IN 查詢效率更高。

但時下面的例外還要看一下：

in適合內外表都很大的情況，exists適合外表結果集很小的情況。
=====================================
今天市場報告有個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確實是要根據表的資料量來決定使用。