其实看到这个标题很多小伙伴就笑了，这个话题真的是永垂不朽，各种面试专用题目，从我刚开始接触Oracle的时候就开始有这个问题了，那时候还没什么人去研究这俩
所以基本你去百度和论坛搜索的话基本得到的一致答复就是exists比in快，而理由无非是exists可以用到索引，exists先走内查询等等等等。
但这些都是不完全正确的，对于初学可以暂时这样理解，但是我们始终是要成长的，不能总停留在别人的实验结果上，既要知其然，也要知其所以然。

这里我集各家之所长，加上自己的实验和理论也说下我的看法~

“in 和 exists 哪个快？” 如果有人这样问你， 你完全可以忽略这个问题，去跟他讲，in和exists本身并无快慢优劣，只是Oracle的处理机制有所差异而已，存在即是合理，所以不同场景下各有所长。

看下面这个图，这是我刚刚百度出来的结果：


这段话我们先不看他的对错，因为比起6年前的答案要好的太多，从“exists比in快，要用exists替换in的低效语句”已经进化到了开始从in和exists的执行方式来区分两者区别，已经不是一味的抛弃in。
这里的外表和内表听得比较奇怪，我们暂时改为主表和从表，好理解一些。
select .. from A where id in (select id from B)----A为主表，B为从表
所以网上的结论是：
1. in是主从hash，exists是主表作为驱动表NL从表
2. 因为in先查询从表，再查询主表，exists是先查询主表，再查询从表，基于驱动表要小的CBO思想，所以in适合主表大、从表小；exists适合主表小而从表大的情况

但是我想说的是，这句话依旧不完全正确。
我们来看一组实验：
1. 创建实验用表
create table chen_dba_object as select * from dba_objects;
create table chen_user_object as select * from user_objects;
2. 根据网上结论写一个用in的SQL：
可以看出确实是先扫描了从表，并且走了hash，速度也很快。

如果我SQL反过来写呢？
可以看出依旧是CHEN_USER_OBJECT（小表、主表）走了驱动表，所以在正常情况下，CBO评估哪个先扫描表，与IN、Exists无关，CBO是基于成本的优化器，所以和写法无关。

3. 根据网上结论写一个用exists的SQL：

好像没有走网上说的NestLoop呃 (⊙o⊙)…

4. 写个能走NL的SQL：
所以其实到底走NL还是HASH没固定死是in还是exists，一切路径都是基于代价的（CBO）。

那么问题来了，到底哪个快哪个慢呢？
问题在于用in和exists哪个可以是子查询展开，哪个就快。
一般情况下，使用in和exists必定用到了子查询，所以子查询能否展开进行查询重写是优化in/exists的最关键的一步

一般情况下子查询都会展开，但当子查询中的SQL被CBO认为是一个整体、无法展开的时候：无论SQL中是in还是exists，从表都会作为一个整体被NL。
可以看到上面第4步中使用了关键字rownum，执行计划中出现view关键字，说明子查询无法展开，从而走了NestLoop嵌套循环

除了rownum还有类似树形查询level、分析函数rollup、cube等，子查询中的内容要作为整体无法与主表合并关联的时候，都会走循环查询。

5. exists中恐怖的filter
exists中子查询无法展开的时候不会走NestLoop，而是走了filter，如果是小表作为驱动，则没什么太大影响，可以理解为filter就是NestLoop。
但是filter有个比较坑爹特性就是无法修改驱动表，hint也无法修改，永远会先访问主表（也就是网上说的外表），在对从表（内表）访问，然后走filter关联。

filter这个操作在《Cost Based Oracle Fundamental》此书第九章有介绍。filter的操作是对外表的每一行，都要对内表执行一次全表扫描，所以很多时候提到filter都会感到可怕。他其实很像我们熟悉的neested loop，但它的独特之处在于会维护一个hash table。具体不在这里赘述。
ps. 对了 谁要是买了这个书能否借我看2天，这本书真的是太贵了 =。=

所以结论是：在相对较高的Oracle版本中，在现在基本都用11g02里，其实in比exists更好些，因为in至少不会走这蛋疼的filter。
如果出现了filter就一定要想办法处理，加hint不行的情况就要改写SQL，
通常我们可以用外关联或者with as来手动去掉视图，然后再关联过滤，这样可以避免CBO走filter




版权声明：本文为博主原创文章，未经博主允许不得转载。