子查询中的IN与EXISTS的区别(转)

在复杂的查询中，选择正确的子句将会对性能产生很大的影响。考虑一下在你的编码中使用过哪一些子句。 在主要/明细关系表中写一个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[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-923329/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9650775/viewspace-923329/