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

    在復(fù)雜的查詢中，選擇正確的子句將會對性能產(chǎn)生很大的影響。考慮一下在你的編碼中使用過哪一些子句。

     在主要/明細(xì)關(guān)系表中寫一個SQL的時候，多數(shù)人都會經(jīng)歷這么一步，那就是決定是使用WHERE　EXISTS（…）子句還是WHERE值IN（…）子句來編寫查詢語句。你可能會拒絕使用WHERE　EXISTS，因?yàn)橛盟鼇砭帉懙脑�，要返回一個值，在語法上很困難，而這正是你經(jīng)常忽視的。

     可是，如果你使用基于規(guī)則的最優(yōu)化的話，情況就會大不相同了。你可以通過了解哪個表是驅(qū)動表，以及每一部份會返回多少行，來確定一個基于規(guī)則的查詢的性能。

    當(dāng)你用IN子句來寫一個查詢語句的時候，就等于你向該基于規(guī)則的最優(yōu)化傳達(dá)了這樣一個信息，即你想讓內(nèi)部的查詢推動外部的查詢（假定：IN＝由里而外）。舉例來說，為在一個有14行記錄的EMP表中查詢員工名稱等于“KING”的所有記錄到一個直接報表中，你可以這樣寫：

    select ename from emp e

        where mgr in (select empno from emp where ename = 'KING');

    以下是關(guān)于這個查詢的說明計劃:

    OBJECT     OPERATION

    ---------- ----------------------------------------

                 SELECT STATEMENT()

                  NESTED LOOPS()

    EMP                TABLE ACCESS(FULL)

    EMP                 TABLE ACCESS(BY INDEX ROWID)

    PK_EMP               INDEX(UNIQUE SCAN)

    這個查詢實(shí)際上等同于以下這個:

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

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

    select ename from emp e

        where exists (select 0 from emp where e.mgr = empno and ename = 'KING');

    當(dāng)你在一個WHERE子句中寫EXISTS時，又等于向最優(yōu)化傳達(dá)了這樣一條信息，即你想讓外部查詢先運(yùn)行，使用每一個值來從內(nèi)部查詢（假定：EXISTS＝由外而內(nèi)）中得到一個值。

    關(guān)于這個查詢的說明計劃如下：

    OBJECT     OPERATION

    ---------- ----------------------------------------

                 SELECT STATEMENT()

                  FILTER()

    EMP                TABLE ACCESS(FULL)

    EMP                 TABLE ACCESS(BY INDEX ROWID)

    PK_EMP               INDEX(UNIQUE SCAN)

    這實(shí)際上與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;

    為了確定在基于規(guī)則的最優(yōu)化中，哪一種子句性能更佳，不妨考慮一下，與外部查詢相比，內(nèi)部查詢會返回多少行記錄。許多情況下，EXISTS的表現(xiàn)更突出，這是因?yàn)�，它需要你指定一個加入條件，這就可以調(diào)用一個INDEX掃描。盡管如此，如果該查詢的結(jié)果很小的話，IN常常表現(xiàn)得更好。你通常都愿意運(yùn)行那些能首先返回較少的結(jié)果的查詢。