关于in和exists

最新推荐文章于 2022-11-24 15:35:16 发布
最新推荐文章于 2022-11-24 15:35:16 发布
阅读量93 收藏
点赞数
其实看到这个标题很多小伙伴就笑了，这个话题真的是永垂不朽，各种面试专用题目，从我刚开始接触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：

点击(此处)折叠或打开

  1. SQL> set linesize 999
  2. SQL> set autotrace traceonly;
  3. SQL> SELECT *
  4.   2 FROM CHEN_DBA_OBJECT T
  5.   3 WHERE T.OBJECT_ID IN
  6.   4 (SELECT A.OBJECT_ID FROM CHEN_USER_OBJECT A );

  8. 已选择11行。


  11. 执行计划
  12. ----------------------------------------------------------
  13. Plan hash value: 52863286

  15. -----------------------------------------------------------------------------------------
  16. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  17. -----------------------------------------------------------------------------------------
  18. | 0 | SELECT STATEMENT | | 12 | 2640 | 296 (2)| 00:00:04 |
  19. |* 1 | HASH JOIN RIGHT SEMI| | 12 | 2640 | 296 (2)| 00:00:04 |
  20. | 2 | TABLE ACCESS FULL | CHEN_USER_OBJECT | 12 | 156 | 3 (0)| 00:00:01 |
  21. | 3 | TABLE ACCESS FULL | CHEN_DBA_OBJECT | 68247 | 13M| 292 (2)| 00:00:04 |
  22. -----------------------------------------------------------------------------------------

  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------

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

  29. Note
  30. -----
  31.    - dynamic sampling used for this statement (level=2)


  34. 统计信息
  35. ----------------------------------------------------------
  36.           0 recursive calls
  37.           0 db block gets
  38.        1042 consistent gets
  39.           0 physical reads
  40.           0 redo size
  41.        2179 bytes sent via SQL*Net to client
  42.         519 bytes received via SQL*Net from client
  43.           2 SQL*Net roundtrips to/from client
  44.           0 sorts (memory)
  45.           0 sorts (disk)
  46.          11 rows processed
可以看出确实是先扫描了从表，并且走了hash，速度也很快。

如果我SQL反过来写呢？

点击(此处)折叠或打开

  1. SQL> SELECT *
  2.   2 FROM CHEN_USER_OBJECT T
  3.   3 WHERE T.OBJECT_ID IN
  4.   4 (SELECT A.OBJECT_ID FROM CHEN_DBA_OBJECT A);

  6. 已选择11行。


  9. 执行计划
  10. ----------------------------------------------------------
  11. Plan hash value: 4193304006

  13. ---------------------------------------------------------------------------------------
  14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  15. ---------------------------------------------------------------------------------------
  16. | 0 | SELECT STATEMENT | | 12 | 2436 | 295 (2)| 00:00:04 |
  17. |* 1 | HASH JOIN SEMI | | 12 | 2436 | 295 (2)| 00:00:04 |
  18. | 2 | TABLE ACCESS FULL| CHEN_USER_OBJECT | 12 | 2280 | 3 (0)| 00:00:01 |
  19. | 3 | TABLE ACCESS FULL| CHEN_DBA_OBJECT | 68247 | 866K| 291 (1)| 00:00:04 |
  20. ---------------------------------------------------------------------------------------

  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------

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

  27. Note
  28. -----
  29.    - dynamic sampling used for this statement (level=2)


  32. 统计信息
  33. ----------------------------------------------------------
  34.           7 recursive calls
  35.           0 db block gets
  36.        1109 consistent gets
  37.           0 physical reads
  38.           0 redo size
  39.        2100 bytes sent via SQL*Net to client
  40.         519 bytes received via SQL*Net from client
  41.           2 SQL*Net roundtrips to/from client
  42.           0 sorts (memory)
  43.           0 sorts (disk)
  44.          11 rows processed
可以看出依旧是CHEN_USER_OBJECT（小表、主表）走了驱动表，所以在正常情况下，CBO评估哪个先扫描表，与IN、Exists无关，CBO是基于成本的优化器，所以和写法无关。

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

点击(此处)折叠或打开

  1. SQL> alter system flush buffer_cache;

  3. 系统已更改。

  5. SQL>
  6. SQL> SELECT *
  7.   2 FROM CHEN_USER_OBJECT T
  8.   3 WHERE exists
  9.   4 (SELECT 1 FROM CHEN_DBA_OBJECT A where a.object_id = t.object_id);

  11. 已选择11行。


  14. 执行计划
  15. ----------------------------------------------------------
  16. Plan hash value: 4193304006

  18. ---------------------------------------------------------------------------------------
  19. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  20. ---------------------------------------------------------------------------------------
  21. | 0 | SELECT STATEMENT | | 12 | 2436 | 295 (2)| 00:00:04 |
  22. |* 1 | HASH JOIN SEMI | | 12 | 2436 | 295 (2)| 00:00:04 |
  23. | 2 | TABLE ACCESS FULL| CHEN_USER_OBJECT | 12 | 2280 | 3 (0)| 00:00:01 |
  24. | 3 | TABLE ACCESS FULL| CHEN_DBA_OBJECT | 68247 | 866K| 291 (1)| 00:00:04 |
  25. ---------------------------------------------------------------------------------------

  27. Predicate Information (identified by operation id):
  28. ---------------------------------------------------

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

  32. Note
  33. -----
  34.    - dynamic sampling used for this statement (level=2)


  37. 统计信息
  38. ----------------------------------------------------------
  39.           0 recursive calls
  40.           0 db block gets
  41.        1042 consistent gets
  42.        1040 physical reads
  43.           0 redo size
  44.        2100 bytes sent via SQL*Net to client
  45.         519 bytes received via SQL*Net from client
  46.           2 SQL*Net roundtrips to/from client
  47.           0 sorts (memory)
  48.           0 sorts (disk)
  49.          11 rows processed

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

4. 写个能走NL的SQL：

点击(此处)折叠或打开

  1. SQL> SELECT *
  2.   2 FROM CHEN_USER_OBJECT T
  3.   3 WHERE T.OBJECT_ID IN (SELECT A.OBJECT_ID FROM CHEN_DBA_OBJECT A where rownum = 1);

  5. 未选定行


  8. 执行计划
  9. ----------------------------------------------------------
  10. Plan hash value: 897367281

  12. -----------------------------------------------------------------------------------------
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  14. -----------------------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT | | 1 | 203 | 5 (0)| 00:00:01 |
  16. | 1 | NESTED LOOPS | | 1 | 203 | 5 (0)| 00:00:01 |
  17. | 2 | VIEW | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 |
  18. |* 3 | COUNT STOPKEY | | | | | |
  19. | 4 | TABLE ACCESS FULL| CHEN_DBA_OBJECT | 68247 | 866K| 2 (0)| 00:00:01 |
  20. |* 5 | TABLE ACCESS FULL | CHEN_USER_OBJECT | 1 | 190 | 3 (0)| 00:00:01 |
  21. -----------------------------------------------------------------------------------------

  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------

  26.    3 - filter(ROWNUM=1)
  27.    5 - filter("T"."OBJECT_ID"="OBJECT_ID")

  29. Note
  30. -----
  31.    - dynamic sampling used for this statement (level=2)


  34. 统计信息
  35. ----------------------------------------------------------
  36.          49 recursive calls
  37.           0 db block gets
  38.         543 consistent gets
  39.           0 physical reads
  40.           0 redo size
  41.        1277 bytes sent via SQL*Net to client
  42.         508 bytes received via SQL*Net from client
  43.           1 SQL*Net roundtrips to/from client
  44.           0 sorts (memory)
  45.           0 sorts (disk)
  46.           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

点击(此处)折叠或打开

  1. SQL> SELECT *
  2.   2 FROM CHEN_DBA_OBJECT T
  3.   3 WHERE exists
  4.   4 (SELECT 1 FROM CHEN_USER_OBJECT A where a.object_id = t.object_id and rownum = 1);

  6. 已选择11行。


  9. 执行计划
  10. ----------------------------------------------------------
  11. Plan hash value: 128792432

  13. ----------------------------------------------------------------------------------------
  14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  15. ----------------------------------------------------------------------------------------
  16. | 0 | SELECT STATEMENT | | 32 | 6624 | 6695 (1)| 00:01:21 |
  17. |* 1 | FILTER | | | | | |
  18. | 2 | TABLE ACCESS FULL | CHEN_DBA_OBJECT | 68247 | 13M| 292 (2)| 00:00:04 |
  19. |* 3 | COUNT STOPKEY | | | | | |
  20. |* 4 | TABLE ACCESS FULL| CHEN_USER_OBJECT | 1 | 13 | 3 (0)| 00:00:01 |
  21. ----------------------------------------------------------------------------------------

  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------

  26.    1 - filter( EXISTS (SELECT 0 FROM "CHEN_USER_OBJECT" "A" WHERE ROWNUM=1 AND
  27.               "A"."OBJECT_ID"=:B1))
  28.    3 - filter(ROWNUM=1)
  29.    4 - filter("A"."OBJECT_ID"=:B1)

  31. Note
  32. -----
  33.    - dynamic sampling used for this statement (level=2)


  36. 统计信息
  37. ----------------------------------------------------------
  38.          13 recursive calls
  39.           0 db block gets
  40.      218721 consistent gets
  41.           0 physical reads
  42.           0 redo size
  43.        2179 bytes sent via SQL*Net to client
  44.         519 bytes received via SQL*Net from client
  45.           2 SQL*Net roundtrips to/from client
  46.           0 sorts (memory)
  47.           0 sorts (disk)
  48.          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




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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26224278/viewspace-1810899/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26224278/viewspace-1810899/

ciyuluo8449
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle嵌套语句中in和exist,oracle中关于in和exists
weixin_36285931的博客
04-14 830
oracle中关于in和exists，not in 和not exists、关联子查询、非关联子查询oracle中关于in和exists，not in 和not existsin和existsin 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。not exists:做NL，对子查询先查，有个虚表，有确定值，所以就算子查询有NULL最终也有值...
in和exists的区别
jxzdezhanhao的博客
03-18 9360
1、in和exists in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in； 例如：表A(小表)，表B(大表) select * from A where cc in(select cc from B)　　-->效率低，用到了A表上cc列的索引； se
oracle中的exists 和not exists 用法详解
arsenal_NO1的专栏
07-07 1502
有两个简单例子，以说明 “exists”和“in”的效率问题1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;    T1数据量小而T2数据量非常大时，T12) select * from T1 where T1.a in (select T2.a from T2) ;     T1数据量非常大而
浅析MySQL中exists与in的使用
weixin_30532973的博客
04-30 444
exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false 如下： se...
MySQL关于in和exists的优化
熙熙小学姐
06-08 804
优化原则：永远是小表驱动大表 select * from A where id in(select id from B) 等价于 for select id from B for select * from A where A.id=B.id 当B表的数据集必须小于A表的数据集时，用in优于exists 也可以不是1，是个常量也行 select * from A ...
oracle中关于in和exists，not in 和 not exists用法与区别
ls1120623840的博客
07-09 1852
参考资料 1 oracle中关于in和exists，not in 和 not existshttp://blogold.chinaunix.net/u/32484/showart_270855.html 2 oracle中的exists 和not exists 用法详解http://hi.baidu.com/qianyiliang/blog/item/54db82c2ca71fe1d0ef477a1.html 3 Oracle select in/exists/not in/not exitshttp://
MySQL in和exists 查询分析
神韵
08-31 2688
in和exists执行时，in是先执行内查询，然后再执行外查询。而exists查询它是先执行外查询，然后再执行内查询（这里的内外查询不理解可以结合上面的分析理解）。exists和in在执行时效率单从执行时间来说差不多，exists要稍微优干in，在使用时一般应该是用exists而不用in。如果内查询得出的结果集记录较少，外查询中的表较大且又有索引时应该用in，反之如果外查询记录较少，内查询中的表大，又有索引时使用exists。...
IN和EXISTS的详解
咸鱼的博客
07-04 8115
从效率来看： 1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ; T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。 2) select * from T1 where T1.a in (select T2.a from T2) ; T1数据量非常大而...
in 和 exists 区别
weixin_41157315的博客
11-24 730
in 和 exists 区别
MySQL中exists和in的区别
热门推荐
向上的"狼"的博客
04-19 1万+
网上很多文章都说exists的效率要高于in，其实不然。只能说不一定，exists和in各有各的使用场景，下面我们总结一下MySQL中exists和in的区别： in关键字 select * from A where id in (select id from B) #等价于 for select id from B：先执行； 子查询 for select id from A where A.id = B.id：再执行外面的查询； 执行过程：in是先查询内表【select id from B】
简述Oracle中in和exists的不同
09-09
在Oracle数据库中，`IN`和`EXISTS`都是用于子查询的比较操作符，但它们在执行方式和性能上存在显著差异。许多人误以为`EXISTS`总是比`IN`更快，但实际情况并非如此，具体表现取决于数据的分布和索引的利用。 1. **...
SQL SERVER中关于exists 和 in的简单分析
09-10
在SQL Server中，`EXISTS` 和 `IN` 是两种常用的子查询操作符，用于比较一个查询结果是否与另一个查询结果相匹配。虽然它们在功能上相似，但在性能和优化方面存在一些差异，这在大数据处理时尤其显著。 1. **EXISTS...
SQL查询中in和exists的区别分析
09-10
在SQL查询中，`IN` 和 `EXISTS` 都是用于子查询的条件运算符，它们在不同的场景下有着不同的性能表现。本篇文章将详细分析这两种方法的区别以及何时使用它们更为合适。 首先，`IN` 运算符用于判断某个字段的值是否...
基于JAVA的厨艺交流平台（Vue.js+SpringBoot+MySQL）
08-01
基于Vue.js和SpringBoot的厨艺交流平台是一个功能丰富的在线社区，旨在为烹饪爱好者提供一个分享和学习烹饪技巧的平台。该平台分为管理后台和用户网页端，支持管理员和普通用户两种角色。管理后台提供对菜谱分类、菜谱信息、食材信息、商品信息和美食日志模块的全面管理功能，包括添加、编辑、删除和查询等操作。用户网页端则为用户提供了一个友好的界面，可以浏览和搜索各种菜谱，查看食材和商品信息，发表自己的美食日志，与其他用户互动交流。整个平台采用现代化的前端技术和后端框架，保证了良好的用户体验和高效的数据处理能力。 演示录屏：https://www.bilibili.com/video/BV1uz42197zu 配套教程：https://www.bilibili.com/video/BV1pW4y1P7GR
层次分析法数学建模论文.doc
最新发布
08-01
层次分析法数学建模论文
操作系统原理试卷及参考答案(A).doc
08-01
操作系统原理试卷及参考答案(A)
客户销售额排名.xlsx.xlsx
08-01
销售管理，客户类型，客户满意度，客户登记，进销存，客户记录 适用人群：学习不同技术领域的小白或进阶学习者；可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。
基于java及C++实现的Arduino智能小车，六种控制方式（超声波避障/跟随，红外循迹/遥控，蓝牙遥控，PS2遥控)+源码
08-01
基于java及C++实现的Arduino智能小车，六种控制方式（超声波避障/跟随，红外循迹/遥控，蓝牙遥控，PS2遥控)+源码，适合毕业设计、课程设计、项目开发。项目源码已经过严格测试，可以放心参考并在此基础上延申使用，详情见md文档~ 基于java及C++实现的Arduino智能小车，六种控制方式（超声波避障/跟随，红外循迹/遥控，蓝牙遥控，PS2遥控)+源码，适合毕业设计、课程设计、项目开发。项目源码已经过严格测试，可以放心参考并在此基础上延申使用，详情见md文档~ 项目简介： arduino程序使用模块化设计，可扩展各类玩法。 目前支持六种控制方式，主要分为程控方式和遥控方式， 程控方式 超声波避障 超声波跟随 红外循线 遥控方式 红外遥控 蓝牙遥控 PS2遥控
mysql in和exists
07-27
MySQL中的IN和EXISTS是用于在查询中进行条件判断的两个关键字。 IN关键字用于判断一个值是否在一个集合中。语法如下： ```sql SELECT column_name(s) FROM table_name WHERE value IN (value1, value2, ...); ``` 在上述语法中，column_name是要查询的列名，table_name是要查询的表名，value1、value2等是要判断的值。如果列中的值与集合中的任何一个值相等，则该行将被返回。 EXISTS关键字用于判断一个子查询是否返回任何行。语法如下： ```sql SELECT column_name(s) FROM table_name WHERE EXISTS (subquery); ``` 在上述语法中，column_name是要查询的列名，table_name是要查询的表名，subquery是一个子查询。如果子查询返回任何行，则该行将被返回。 需要注意的是，IN和EXISTS在功能上是有区别的。IN用于判断一个值是否在一个集合中，而EXISTS用于判断一个子查询是否返回任何行。因此，在使用时需要根据具体的需求选择合适的关键字。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值