oracle where exists 2

Where exists 2
之前按照个人理解讲了基本的select 用法。当然 exists 并不仅仅只能更在select之后。比如update 也可以使用 where exists
继续之前的讲解，我从网上看到说。Where exists 和 In 效率不一样，就来做个试验对比一下如何不同。
首先创建一个测试表 t4
create table t4 as select * from emp;
插入数据
insert into t4 select * from t4;
select count(*) from t4;
COUNT(*)
----------
14680064
commit;
接下来写两个等价的 exists 和 in 的查询根据执行计划 具体来分析一下。
set autot traceonly
select empno,ename from emp where exists (select 1 from t4 where t4.deptno=emp.deptno);
14 rows selected.
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 740 | 43 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 20 | 740 | 43 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 20 | 480 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPTNOIND | 3804K| 47M| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

select a.empno,a.ename from emp a where a.deptno in (select deptno from t4 );
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 740 | 43 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 20 | 740 | 43 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 20 | 480 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPTNOIND | 3804K| 47M| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
从如上看到，两条语句的执行计划是一摸一样的。我又反复测试了几个 exists 和 in 的语句，发现但从执行计划来看，看不出来什么，或许是我写的太简单，于是从网上查询了一些资料结合自己的理解。
想从执行原理去解释一下。
where exists 的原理是循环。之前也说道过，exists 应该是先去循环父表，不断的取出表中的数据。然后将这个取出的数据和 子查询中的条件去联合查询，然后返回值，如果有返回值，则取出这条记录输出，如果记录不匹配则不返回值。
in 的原理如下。
select a.empno,a.ename from emp a where a.deptno in (select deptno from t4); 可以等价替换为
select a.empno,a.ename from emp a,(select distinct deptno from t4) b where a.deptno=b.deptno;
这时就能看出区别来了：
首先 where exists 中会做父表的遍历和对子表的查询（尽管这里的对子表的遍历，应该是只符合条件就会返回，并不一定会完全遍历完子表）。如果在父表小，子表大的情况下，这种写法的效率会很高，并且 t4.deptno=emp.deptno，是可以走索引的。效率不会很差。但是如果父表很大的情况下，这种效率就不会很高。因为要对父表进行遍历（全表扫描）。
而in 的等价替换中的(select distinct deptno from t4)，如果t4 这个表很小的情况下，效率也是非常快的。但是这个语句在 t4 很大的情况下效率是非常低的。首先 oracle 会先挂起 父查询的语句，先去将子查询执行完毕后，再进行关联查询。这时候，如果 父表很大而子表很小，效率就会比 where exists 高。
总的来说，in 和 where exists 在两个表想当的情况下，效率应该是差不多的。
但是如果在父表大子表小的情况下 in 的效率要比 where exists快。
相反如果是在子表大而父表小的情况下这时候where exists 的效率就要比in快了。