oracle開發系列（三）exists&not exists用法(10g)

標籤：exists   in   not exists   filter   anti join   

註：以下內容適合 初學oracle開發或者java等開發人員，高手略過

一 exists&in

以下三個語句  功能都是從 iodso.qos_hisentry_sheet_jtext_td 裡面找到  sheet_no在  iodso.qos_hisentry_sheet_td表 arch_time 1天時間裡面的單子。

iodso.qos_hisentry_sheet_jtext_td 有個普通的聯合索引                  

iodso.qos_hisentry_sheet_td  有個普通的索引                                  

兩個表的資料量情況

select count(1) from iodso.qos_hisentry_sheet_td-- 29843027

select count(1) from  iodso.qos_hisentry_sheet_jtext_td--29973242

1

select *  from iodso.qos_hisentry_sheet_jtext_td t where t.sheet_no in (select a.sheet_no                        from iodso.qos_hisentry_sheet_td a                       where a.arch_time between trunc(sysdate - 1, 'dd') and                             trunc(sysdate, 'dd'));

2

select *  from iodso.qos_hisentry_sheet_jtext_td t where t.sheet_no in (select a.sheet_no                        from iodso.qos_hisentry_sheet_td a                       where a.arch_time between trunc(sysdate - 1, 'dd') and                             trunc(sysdate, 'dd')                         and t.sheet_no = a.sheet_no);

3

select *  from iodso.qos_hisentry_sheet_jtext_td t where exists (select a.sheet_no          from iodso.qos_hisentry_sheet_td a         where a.arch_time between trunc(sysdate - 1, 'dd') and               trunc(sysdate, 'dd')           and t.sheet_no = a.sheet_no);

執行計畫比較

執行計畫由pl/sql Dev的F5鍵產生  ，一般看執行計畫會建議從sqlplus explain plan for看 但是開發人員可能更習慣用pl、sql工具

且工具能定位到第一個執行的地方  且對應的操作描述 在最下方有一串英文 如 sort_unique 的解釋 在最下面紅圈的地方 sort a result set and eliminate duplicates 意思是對結果集排序並且去重

sql 1 的計劃：

sql 3 的計劃：

sql 2的計劃：

從上面的執行計畫及順序來看 三個sql 完全一樣。

執行結果

sql 1的執行結果：

sql2 的執行結果：

sql3 的執行結果：

從以上來看 是sql1 執行的最快 sql2 執行的最慢

上面是從查小表的情況 再看看下面語句的情況（查大表的情況）：

1 

select a.*  from iodso.qos_hisentry_sheet_td a where a.arch_time between trunc(sysdate - 1, 'dd') and       trunc(sysdate, 'dd')   and sheet_no in       (select sheet_no from iodso.qos_hisentry_sheet_jtext_td t);

2

select a.*          from (select *            from iodso.qos_hisentry_sheet_td a           where a.arch_time between trunc(sysdate - 1, 'dd') and                 trunc(sysdate, 'dd')) a         where exists (select t.sheet_no                  from iodso.qos_hisentry_sheet_jtext_td t                 where t.sheet_no = a.sheet_no);

所以 網上很多說的 exists 比 in快 或者 檢索大表的時候 exists比 in快 等等 不一定都是準確的，現在百度的很多東西可能都是複製來複製去，還有的是以前8i 9i老版本的規則 現在基本都是10g以上 不一定適用。網上的結論要慎用 最好自己實驗下。

exists 和 in的效率通常情況是差不多的，需要看執行計畫及實際上執行時間為準，。

ps:大部分的企業級開發人員可能更喜歡用in 易於平常的思維理解

二 not exists&not in
1

select t.occur_area_id-1,  COUNT(1) ALL_NUM,   SUM(CASE             WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN              1             ELSE              0           END) CS_NUM  from  QOS_NET_CONTROL_GD_sb Twhere t.sheet_no not in(SELECT  t1.sheet_no  FROM QOS_NET_CONTROL_GD_sb T1,IODSO.QOS_EOSORG_T_EMPLOYEE     T2,          IODSO.QOS_EOSORG_T_ORGANIZATION T3,       iodso.qos_eosoperator t6 WHERE T1.USERID = T6.userid   and t6.operatorid = t2.operatorid   and t2.orgid=t3.orgid and T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')   AND T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')  )group by t.occur_area_id;

2

select t.occur_area_id - 1,         COUNT(1) ALL_NUM,         SUM(CASE               WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN                1               ELSE                0             END) CS_NUM    from QOS_NET_CONTROL_GD_sb T   where not exists   (select 1            from QOS_NET_CONTROL_GD_sb           s,                 IODSO.QOS_EOSORG_T_EMPLOYEE     T2,                 IODSO.QOS_EOSORG_T_ORGANIZATION T3,                 iodso.qos_eosoperator           t6           where T.Sheet_No = s.sheet_no             and s.USERID = T6.userid             and t6.operatorid = t2.operatorid             and t2.orgid = t3.orgid)             and T.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')   group by t.occur_area_id

從上面執行計畫可以看到 cost 差別很大 ，not exists 比not in 的小很多。 not exists使用的是hash join anti 而 not in 使用的是filter。執行時間來看 not exists 幾分鐘 not in 執行了30分鐘還沒完成。

小總結：(此內容轉)
Semi-join
通常出現在使用了exists或in的sql中，所謂semi-join即在兩表關聯時，當第二個表中存在一個或多個匹配記錄時，返回第一個表的記錄；
與普通join的區別在於semi-join時，第一個表裡的記錄最多隻返回一次

Anti-join
第二張表沒有發現匹配記錄時，才會返回第一張表裡的記錄；
何時選擇anti-join1
使用not in且相應列有not null約束
not exists，不保證每次都用到anti-join
當無法選擇anti-join時，oracle常會採用filter替代

filter
是對外表的每一行，都要對內表執行一次全表掃描，他其實很像我們熟悉的neested loop，但它的獨特之處在於會維護一個hash table

三 兩個表根據某欄位關聯更新

update ap   set ap.t =       (select bp.t from bp where ap.s = bp.s) where exists (select 1 from bp where ap.s = bp.s);commit;

語句看似很簡單 但是當ap  bp本身都是很複雜的查詢的時候 可能想到這個比較困難了。

oracle開發系列（三）exists&not exists用法(10g)