子查询中的IN与EXISTS的区别

作者：Builder.com 来源:

 

在复杂的查询中，选择正确的子句将会对性能产生很大的影响。考虑一下在你的编码中使用过哪一些子句。

 

在主要/明细关系表中写一个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

 

---

本文作者: Scott Stephens已经在Oracle工作了13年有余，他曾经在技术支持、电子商务、市场和软件开发等部门工作。

---