关于in和exists

浏览: 2649

其实看到这个标题很多小伙伴就笑了，这个话题真的是永垂不朽，各种面试专用题目，从我刚开始接触Oracle的时候就开始有这个问题了，那时候还没什么人去研究这俩

所以基本你去百度和论坛搜索的话基本得到的一致答复就是exists比in快，而理由无非是exists可以用到索引，exists先走内查询等等等等。

但这些都是不完全正确的，对于初学可以暂时这样理解，但是我们始终是要成长的，不能总停留在别人的实验结果上，既要知其然，也要知其所以然。

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

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

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

Clipboard Image.png

这段话我们先不看他的对错，因为比起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：

SQL> set linesize 999

SQL> set autotrace traceonly;

SQL> SELECT *

  2 FROM CHEN_DBA_OBJECT T

  3 WHERE T.OBJECT_ID IN

  4 (SELECT A.OBJECT_ID FROM CHEN_USER_OBJECT A );

已选择11行。

执行计划

----------------------------------------------------------

Plan hash value: 52863286

-----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12 | 2640 | 296 (2)| 00:00:04 |

|* 1 | HASH JOIN RIGHT SEMI| | 12 | 2640 | 296 (2)| 00:00:04 |

| 2 | TABLE ACCESS FULL | CHEN_USER_OBJECT | 12 | 156 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL | CHEN_DBA_OBJECT | 68247 | 13M| 292 (2)| 00:00:04 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T"."OBJECT_ID"="A"."OBJECT_ID")

Note

-----

   - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

          0 recursive calls

          0 db block gets

       1042 consistent gets

          0 physical reads

          0 redo size

       2179 bytes sent via SQL*Net to client

        519 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

         11 rows processed

可以看出确实是先扫描了从表，并且走了hash，速度也很快。

如果我SQL反过来写呢？

SQL> SELECT *

  2 FROM CHEN_USER_OBJECT T

  3 WHERE T.OBJECT_ID IN

  4 (SELECT A.OBJECT_ID FROM CHEN_DBA_OBJECT A);

已选择11行。

执行计划

----------------------------------------------------------

Plan hash value: 4193304006

---------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12 | 2436 | 295 (2)| 00:00:04 |

|* 1 | HASH JOIN SEMI | | 12 | 2436 | 295 (2)| 00:00:04 |

| 2 | TABLE ACCESS FULL| CHEN_USER_OBJECT | 12 | 2280 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| CHEN_DBA_OBJECT | 68247 | 866K| 291 (1)| 00:00:04 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T"."OBJECT_ID"="A"."OBJECT_ID")

Note

-----

   - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

          7 recursive calls

          0 db block gets

       1109 consistent gets

          0 physical reads

          0 redo size

       2100 bytes sent via SQL*Net to client

        519 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

         11 rows processed

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

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

SQL> alter system flush buffer_cache;

系统已更改。

SQL>

SQL> SELECT *

  2 FROM CHEN_USER_OBJECT T

  3 WHERE exists

  4 (SELECT 1 FROM CHEN_DBA_OBJECT A where a.object_id = t.object_id);

已选择11行。

执行计划

----------------------------------------------------------

Plan hash value: 4193304006

---------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12 | 2436 | 295 (2)| 00:00:04 |

|* 1 | HASH JOIN SEMI | | 12 | 2436 | 295 (2)| 00:00:04 |

| 2 | TABLE ACCESS FULL| CHEN_USER_OBJECT | 12 | 2280 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| CHEN_DBA_OBJECT | 68247 | 866K| 291 (1)| 00:00:04 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."OBJECT_ID"="T"."OBJECT_ID")

Note

-----

   - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

          0 recursive calls

          0 db block gets

       1042 consistent gets

       1040 physical reads

          0 redo size

       2100 bytes sent via SQL*Net to client

        519 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

         11 rows processed

Clipboard Image.png

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

4. 写个能走NL的SQL：

SQL> SELECT *

  2 FROM CHEN_USER_OBJECT T

  3 WHERE T.OBJECT_ID IN (SELECT A.OBJECT_ID FROM CHEN_DBA_OBJECT A where rownum = 1);

未选定行

执行计划

----------------------------------------------------------

Plan hash value: 897367281

-----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 203 | 5 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 203 | 5 (0)| 00:00:01 |

| 2 | VIEW | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 |

|* 3 | COUNT STOPKEY | | | | | |

| 4 | TABLE ACCESS FULL| CHEN_DBA_OBJECT | 68247 | 866K| 2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | CHEN_USER_OBJECT | 1 | 190 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(ROWNUM=1)

   5 - filter("T"."OBJECT_ID"="OBJECT_ID")

Note

-----

   - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

         49 recursive calls

          0 db block gets

        543 consistent gets

          0 physical reads

          0 redo size

       1277 bytes sent via SQL*Net to client

        508 bytes received via SQL*Net from client

          1 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          0 rows processed

所以其实到底走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

SQL> SELECT *

  2 FROM CHEN_DBA_OBJECT T

  3 WHERE exists

  4 (SELECT 1 FROM CHEN_USER_OBJECT A where a.object_id = t.object_id and rownum = 1);

已选择11行。

执行计划

----------------------------------------------------------

Plan hash value: 128792432

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 32 | 6624 | 6695 (1)| 00:01:21 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | CHEN_DBA_OBJECT | 68247 | 13M| 292 (2)| 00:00:04 |

|* 3 | COUNT STOPKEY | | | | | |

|* 4 | TABLE ACCESS FULL| CHEN_USER_OBJECT | 1 | 13 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "CHEN_USER_OBJECT" "A" WHERE ROWNUM=1 AND

              "A"."OBJECT_ID"=:B1))

   3 - filter(ROWNUM=1)

   4 - filter("A"."OBJECT_ID"=:B1)

Note

-----

   - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

         13 recursive calls

          0 db block gets

     218721 consistent gets

          0 physical reads

          0 redo size

       2179 bytes sent via SQL*Net to client

        519 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

         11 rows processed

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

推荐 2
BAO胖子 fadazhao
本文由 老头子 创作，采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者，并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

13 个评论

Jmarry

Jmarry

一般，IN后面的数据量太多，最好不要用。。效率太差，已遇到N多次。
2015-10-10 14:52
老头子

老头子 回复 Jmarry

感觉白写了... =.=
2015-10-10 14:56
cognos小白

cognos小白

这个话题，我们也经常讨论
2015-10-10 15:01
BAO胖子

BAO胖子 回复 老头子

你可能得先写个怎么读query plan的文章....
2015-10-10 15:11
choc

choc

"因为in先查询从表，再查询主表，exists是先查询主表，再查询从表，基于驱动表要小的CBO思想，所以in适合主表大、从表小；exists适合主表小而从表大的情况"这个结论是正确的吗？我理解是正确的
2016-02-23 16:19
老头子

老头子 回复 choc

视情况而定，不完全正确，哪个作为驱动表是CBO决定的，不是语法决定的，但有些场景会导致你说的情况，并非所有场景，具体要视执行计划而看。
2016-02-23 23:27
dota_2261

dota_2261

看了很久，有一个问题不太理解，在例4中，有一句话：子查询能否展开进行查询重写是优化in/exists的最关键的一步，如何知道子查询能否展开？
2016-02-26 11:37
老头子

老头子 回复 dota_2261

看执行计划，或oracle自身无法展开，比如展开会引起结果不正确等，看执行计划是最直接的
2016-02-26 11:46
dota_2261

dota_2261 回复 老头子

执行计划哪里可以看出子查询是否展开？能够做一个讲述如何使用执行计划来优化查询的博客或者课程就最好了，呵呵
2016-02-26 11:54
choc

choc

有时候in和exists的执行计划完全一样。

我遇到过一次。

Not Exists的比Not in快的不是一点点。

另外，Left join改写后比Not Exists更快。
2016-03-28 14:25
老头子

老头子 回复 choc

什么情况都有可能发生
你觉得Not exists 比 Not in快是因为你没遇到Not in比Not exists快的情况 ：）
关联始终是最快的，所以能改写成外关联就最好改写成外关联
2016-03-28 14:37
choc

choc

那么in改写为inner join是否一定比in快呢？
我看到的执行计划也一样，当然我见到的SQL有限

我理解in 应该都可以改写为inner join吧？

另外，什么时候not in 不能改写为left join呢？
2016-03-28 14:40
老头子

老头子 回复 choc

看业务逻辑，当改写后业务逻辑错了的时候就不能改写，哈哈
2016-03-28 15:13

要回复文章请先登录或注册