子查詢中的IN與EXISTS的區別

　　在復雜的查詢中選擇正確的子句將會對性能產生很大的影響考慮一下在你的編碼中使用過哪一些子句
　　
　　在主要/明細關系表中寫一個SQL的時候多數人都會經歷這麼一步那就是決定是使用WHERE　EXISTS（…）子句還是WHERE值IN（…）子句來編寫查詢語句你可能會拒絕使用WHERE　EXISTS因為用它來編寫的話要返回一個值在語法上很困難而這正是你經常忽視的
　　
　　可是如果你使用基於規則的最優化的話情況就會大不相同了你可以通過了解哪個表是驅動表以及每一部份會返回多少行來確定一個基於規則的查詢的性能
　　
　　當你用IN子句來寫一個查詢語句的時候就等於你向該基於規則的最優化傳達了這樣一個信息即你想讓內部的查詢推動外部的查詢（假定IN＝由裡而外）舉例來說為在一個有行記錄的EMP表中查詢員工名稱等於KING的所有記錄到一個直接報表中你可以這樣寫
　　
　　select ename from emp e
　　
　　　　where mgr in (select empno from emp where ename = KING);
　　
　　以下是關於這個查詢的說明計劃:
　　
　　OBJECT　　 OPERATION
　　
　　　　　　　　　　 SELECT STATEMENT()
　　
　　　　　　　　　　　NESTED LOOPS()
　　
　　EMP　　　　　　　　TABLE ACCESS(FULL)
　　
　　EMP　　　　　　　　 TABLE ACCESS(BY INDEX ROWID)
　　
　　PK_EMP　　　　　　　 INDEX(UNIQUE SCAN)
　　
　　這個查詢實際上等同於以下這個:
　　
　　select eename from emp e(select empno from emp where ename = KING) e　where emgr = eempno;
　　
　　你可以用EXISTS寫同樣的查詢你只要把外部查詢一欄移到一個像下面這樣的子查詢環境中就可以了
　　
　　select ename from emp e
　　
　　　　where exists (select from emp where emgr = empno and ename = KING);
　　
　　當你在一個WHERE子句中寫EXISTS時又等於向最優化傳達了這樣一條信息即你想讓外部查詢先運行使用每一個值來從內部查詢（假定EXISTS＝由外而內）中得到一個值
　　
　　關於這個查詢的說明計劃如下
　　
　　OBJECT　　 OPERATION
　　
　　　　　　　　　　 SELECT STATEMENT()
　　
　　　　　　　　　　　FILTER()
　　
　　EMP　　　　　　　　TABLE ACCESS(FULL)
　　
　　EMP　　　　　　　　 TABLE ACCESS(BY INDEX ROWID)
　　
　　PK_EMP　　　　　　　 INDEX(UNIQUE SCAN)
　　
　　這實際上與PL/SQL編碼類似
　　
　　set serveroutput on;
　　
　　declare
　　
　　　　l_count integer;
　　
　　begin
　　
　　　　for e in (select mgrename from emp) loop
　　
　　　　　　select count(*) into l_count from emp
　　
　　　　　　 where emgr = empno and ename = KING;
　　
　　　　　　if l_count != then
　　
　　　　　　　　dbms_outputput_line(eename);
　　
　　　　　　end if;
　　
　　　　end loop;
　　
　　end;
　　為了確定在基於規則的最優化中哪一種子句性能更佳不妨考慮一下與外部查詢相比內部查詢會返回多少行記錄許多情況下EXISTS的表現更突出這是因為它需要你指定一個加入條件這就可以調用一個INDEX掃描盡管如此如果該查詢的結果很小的話IN常常表現得更好你通常都願意運行那些能首先返回較少的結果的查詢
　　
　　有些人盡量避免使用EXISTS子句這是因為它要求必須從該查詢中返回一個結果縱使這個結果根本就不會用到由於個人喜好的原因人們經常使用x或零從說明計劃的輸出我們可以看到它顯示了最優化會一直使用而拒絕接受你所有輸入的其它任何值許多開發人員有這樣一種習慣那就是經常輸入一些常量
　　
　　如果你想運行一下你自己的測試或者想看看其它的例子以下是我使用的兩個腳本
　　
　　REM explainsql view plan from PLAN_TABLE
　　set feedback off
　　set verify off
　　set pages
　　column operation format a
　　column object format a
　　
　　TTITLE * STATEMENT_ID = & *
　　select object_name object
　　　　　 lpad( level)||operation||(||options||) operation
　　　from plan_table
　　 start with id = and statement_id = &
　　 connect by prior id = parent_id and statement_id = &;
　　
　　REM existssql examples with EXPLAIN PLAN
　　REM IN vs EXISTS
　　
　　REM if you dont have a PLAN_TABLE run
　　REM @?/rdbms/admin/xplan
　　alter session set optimizer_goal = rule;
　　truncate table plan_table;
　　
　　REM find direct reports to KING
　　explain plan set statement_id = IN for
　　select ename from emp e
　　　　where mgr in (select empno from emp where ename = KING);
　　
　　explain plan set statement_id = JOININ for
　　select eename from emp e(select empno from emp where ename = KING) e
　　　　where emgr = eempno;
　　
　　explain plan set statement_id = EXISTS for
　　select ename from emp e
　　　　where exists (select from emp where emgr = empno and ename = KING);
　　
　　explain plan set statement_id = = for
　　select ename from emp e
　　　　where mgr = (select empno from emp where ename = KING);
　　
　　explain plan set statement_id = JOIN for
　　select eename from emp eemp e
　　 where emgr = eempno
　　　 and eename = KING;
　　
　　REM find employees with greater than average salaries
　　explain plan set statement_id = > for
　　select ename from emp e where esal > (select avg(sal) from emp);
　　
　　explain plan set statement_id = JOIN for
　　select eename from emp e(select avg(sal) sal from emp) e
　　 where esal > esal;
　　
　　@@explain IN
　　@@explain JOININ
　　@@explain EXISTS
　　@@explain =
　　@@explain JOIN
　　@@explain >
　　@@explain JOIN
From:http://tw.wingwit.com/Article/os/youhua/201404/30389.html