子查詢中的IN與EXISTS的區別(轉)

子查詢中的IN與EXISTS的區別(轉)[@more@]

　　在複雜的查詢中，選擇正確的子句將會對效能產生很大的影響。考慮一下在你的編碼中使用過哪一些子句。

　　

　　在主要/明細關係表中寫一個SQL的時候，多數人都會經歷這麼一步，那就是決定是使用WHERE　EXISTS（…）子句還是WHERE值IN（…）子句來編寫查詢語句。你可能會拒絕使用WHERE　EXISTS，因為用它來編寫的話，要返回一個值，在語法上很困難，而這正是你經常忽視的。

　　

　　可是，如果你使用基於規則的最最佳化的話，情況就會大不相同了。你可以透過了解哪個表是驅動表，以及每一部份會返回多少行，來確定一個基於規則的查詢的效能。

　　

　　當你用IN子句來寫一個查詢語句的時候，就等於你向該基於規則的最最佳化傳達了這樣一個資訊，即你想讓內部的查詢推動外部的查詢（假定：IN＝由裡而外）。舉例來說，為在一個有14行記錄的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 e1.ename from emp e1,(select empno from emp where ename = 'KING') e2　where e1.mgr = e2.empno;

　　

　　你可以用EXISTS寫同樣的查詢，你只要把外部查詢一欄移到一個像下面這樣的子查詢環境中就可以了：

　　

　　select ename from emp e

　　

　　　　where exists (select 0 from emp where e.mgr = 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 mgr,ename from emp) loop

　　

　　　　　　select count(*) into l_count from emp

　　

　　　　　　 where e.mgr = empno and ename = 'KING';

　　

　　　　　　if l_count != 0 then

　　

　　　　　　　　dbms_output.put_line(e.ename);

　　

　　　　　　end if;

　　

　　　　end loop;

　　

　　end;

　　為了確定在基於規則的最最佳化中，哪一種子句效能更佳，不妨考慮一下，與外部查詢相比，內部查詢會返回多少行記錄。許多情況下，EXISTS的表現更突出，這是因為，它需要你指定一個加入條件，這就可以呼叫一個INDEX掃描。儘管如此，如果該查詢的結果很小的話，IN常常表現得更好。你通常都願意執行那些能首先返回較少的結果的查詢。

　　

　　有些人儘量避免使用EXISTS子句，這是因為，它要求必須從該查詢中返回一個結果，縱使這個結果根本就不會用到。由於個人喜好的原因，人們經常使用‘x’,1,0或零。從說明計劃的輸出我們可以看到，它顯示了，最最佳化會一直使用0而拒絕接受你所有輸入的其它任何值。許多開發人員有這樣一種習慣，那就是經常輸入一些常量。

　　

　　如果你想執行一下你自己的測試，或者想看看其它的例子，以下是我使用的兩個指令碼：

　　

　　REM -- explain.sql - view plan from PLAN_TABLE

　　set feedback off

　　set verify off

　　set pages 2000

　　column operation format a40

　　column object format a10

　　

　　TTITLE * STATEMENT_ID = '&1' *

　　select object_name object,

　　　　　 lpad(' ',level-1)||operation||'('||options||')' operation

　　　from plan_table

　　 start with id = 0 and statement_id = '&1'

　　 connect by prior id = parent_id and statement_id = '&1';

　　

　　REM -- exists.sql - examples with EXPLAIN PLAN

　　REM -- IN vs. EXISTS

　　

　　REM -- if you don't 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 = 'JOIN-IN' for

　　select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2

　　　　where e1.mgr = e2.empno;

　　

　　explain plan set statement_id = 'EXISTS' for

　　select ename from emp e

　　　　where exists (select 0 from emp where e.mgr = 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 = 'JOIN1' for

　　select e1.ename from emp e1,emp e2

　　 where e1.mgr = e2.empno

　　　 and e2.ename = 'KING';

　　

　　REM -- find employees with greater than average salaries

　　explain plan set statement_id = '>' for

　　select ename from emp e where e.sal > (select avg(sal) from emp);

　　

　　explain plan set statement_id = 'JOIN2' for

　　select e1.ename from emp e1,(select avg(sal) sal from emp) e2

　　 where e1.sal > e2.sal;

　　

　　@@explain IN

　　@@explain JOIN-IN

　　@@explain EXISTS

　　@@explain =

　　@@explain JOIN1

　　@@explain >

　　@@explain JOIN2