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快了。