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

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

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

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

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

    select ename from emp e

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

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

    OBJECT     OPERATION

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

                 SELECT STATEMENT()

                  NESTED LOOPS()

    EMP                TABLE ACCESS(FULL)

    EMP                 TABLE ACCESS(BY INDEX ROWID)

    PK_EMP               INDEX(UNIQUE SCAN)

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

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

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

    select ename from emp e

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

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

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

    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)部查詢會(huì)返回多少行記錄。許多情況下，EXISTS的表現(xiàn)更突出，這是因?yàn)椋�它需要你指定一個(gè)加入條件，這就可以調(diào)用一個(gè)INDEX掃描。盡管如此，如果該查詢的結(jié)果很小的話，IN常常表現(xiàn)得更好。你通常都愿意運(yùn)行那些能首先返回較少的結(jié)果的查詢。