经常别人说EXISTS比IN快！NOT EXISTS比NOT IN快！然而事实真的如此么？

??? 我们先讨论IN和EXISTS。
??? select * from t1 where x in ( select y from t2 )
??? 事实上可以理解为：
??? select *
????? from t1, ( select distinct y from t2 ) t2
???? where t1.x = t2.y;
??? ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。
??? select * from t1 where exists ( select null from t2 where y = x )
??? 可以理解为：
??? for x in ( select * from t1 )
??? loop
?????? if ( exists ( select null from t2 where y = x.x )
?????? then
????????? OUTPUT THE RECORD!
?????? end if
??? end loop
??? ——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。
??? 综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
??? 如果你对上述说法表示怀疑，请看以下测试：
********************************************************************************
SQL> create table big as select * from all_objects;

表已创建。

SQL> insert /*+ append */ into big select * from big;

已创建26872行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into big select * from big;

已创建53744行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into big select * from big;

已创建107488行。

SQL> commit;

提交完成。

SQL> create index big_idx on big(object_id);

索引已创建。

SQL> create table small as select * from all_objects where rownum < 100;

表已创建。

SQL> create index small_idx on small(object_id);

索引已创建。
********************************************************************************
运行SQL并设置EVENT=10046，用TKPROF格式化TRACE文件，结果如下。

大表在外，小表在内的测试：
********************************************************************************
select count(subobject_name)
from big
where object_id in ( select object_id from small )

call????   count??????   cpu???   elapsed??????   disk?????   query???   current???????   rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse???????   1?????   0.00??????   0.01?????????   0?????????   0?????????   0??????????   0
Execute?????   1?????   0.00??????   0.00?????????   0?????????   0?????????   0??????????   0
Fetch???????   2?????   0.00??????   0.14????????   29???????   900?????????   0??????????   1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total???????   4?????   0.00??????   0.15????????   29???????   900?????????   0??????????   1

Rows????   Execution Plan
-------  ---------------------------------------------------
?????   0  SELECT STATEMENT??   GOAL: CHOOSE
?????   1??   SORT (AGGREGATE)
???   792???   TABLE ACCESS (BY INDEX ROWID) OF 'BIG'
???   892????   NESTED LOOPS
????   99?????   VIEW OF 'VW_NSO_1'
????   99??????   SORT (UNIQUE)
????   99???????   TABLE ACCESS (FULL) OF 'SMALL'
???   792?????   INDEX (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE)

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id )

call????   count??????   cpu???   elapsed??????   disk?????   query???   current???????   rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse???????   1?????   0.00??????   0.00?????????   0?????????   0?????????   0??????????   0
Execute?????   1?????   0.00??????   0.00?????????   0?????????   0?????????   0??????????   0
Fetch???????   2?????   1.90??????   2.72??????   2917????   216125?????????   0??????????   1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total???????   4?????   1.90??????   2.72??????   2917????   216125?????????   0??????????   1

Rows????   Execution Plan
-------  -------------------------------------------