浅谈Oracle中exists与in的执行效率问题_ITPUB博客

相关文章推荐

愤怒的菠菜 · python解决 ...· 4 月前 ·

爱看球的企鹅 · 王者荣耀：用数据说话，金色圣剑是否能够取代末 ...· 6 月前 ·

开心的炒粉 · 拜托别吃我漫画免费 - 拜托别吃我漫画 - ...· 1 年前 ·

安静的毛豆 · HTTP长连接和短连接 - 白露~ - 博客园· 1 年前 ·

风流倜傥的蛋挞 · 成都至东南亚公路货运模式开启-新华网· 1 年前 ·

in 是把外表和内表作 hash join ，而 exists 是对外表作 loop ，每次 loop 再对内表进行查询。一般大家都认为 exists 比 in 语句的效率要高，这种说法其实是不准确的，这个是要区分环境的。 exists对外表用 loop 逐条查询，每次查询都会查看 exists 的条件语句，当 exists 里的条件语句能够返回记录行时 ( 无论记录行是的多少，只要能返回 ) ，条件就为真，返回当前 loop 到的这条记录，反之如果 exists 里的条件语句不能返回记录行，则当前 loop 到的这条记录被丢弃， exists 的条件就像一个 bool 条件，当能返回结果集则为 true ，不能返回结果集则为 false 。 exists()会执行 A.length 次，它并不缓存 exists() 结果集，因为 exists() 结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回 false ，非空则返回 true 。
它的查询过程类似于以下过程：当 B 表比 A 表数据大时适合使用 exists() ，因为它没有那么多遍历操作，只需要再执行一次查询就行。
如： A 表有 10000 条记录， B 表有 1000000 条记录，那么 exists() 会执行 10000 次去判断 A 表中的 id 是否与 B 表中的 id 相等。
如： A 表有 10000 条记录， B 表有 100000000 条记录，那么 exists() 还是执行 10000 次，因为它只执行 A.length 次，可见 B 表数据越多，越适合 exists() 发挥效果。
再如： A 表有 10000 条记录， B 表有 100 条记录，那么 exists() 还是执行 10000 次，还不如使用 in() 遍历 10000*100 次，因为 in() 是在内存里遍历比较，而 exists() 需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。
结论： exists() 适合 B 表比 A 表数据大的情况 可以看出，当 B 表数据较大时不适合使用 in() ，因为它会 B 表数据全部遍历一次
如： A 表有 10000 条记录， B 表有 1000000 条记录，那么最多有可能遍历 10000*1000000 次，效率很差。
再如： A 表有 10000 条记录， B 表有 100 条记录，那么最多有可能遍历 10000*100 次，遍历次数大大减少，效率大大提升。
结论： in() 适合 B 表比 A 表数据小的情况 在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录。
insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);
EXISTS 与 IN 的使用效率的问题，通常情况下采用 exists 要比 in 效率高，因为 IN 不走索引。但要看实际情况具体使用： IN 适合于外表大而内表小的情况； EXISTS 适合于外表小而内表大的情况。 SEIANG@seiang11g> select * from wjq1 where exists (select 1 from wjq2 where wjq1.object_name=wjq2.table_name and wjq2.table_name like 'M%'); SEIANG@seiang11g> select /*+ rule*/ * from wjq1 where exists (select 1 from wjq2 where wjq1.object_name=wjq2.table_name and wjq2.table_name like 'M%'); 通过上面两个执行计划的对比发现：
在这里，我们可以看到实际上，使用in效率比exists效率更高。我们可以这样来理解这种情况：
对于in，RBO优化器选择的内存查询的结果作为驱动表来进行nest loops连接，所以当内存查询的结果集比较小的时候，这个in的效率还是比较高的。
对于exists，RBO优化器则是利用外查询表的全表扫描结果集过滤内查询的结果集，当外查询的表比较大的时候，相对效率比较低。 SEIANG@seiang11g> select * from wjq2 where exists (select 1 from wjq1 where wjq1.object_name=wjq2.table_name and wjq1.object_name like 'S%');
广播电视节目制作经营许可证(京) 字第1234号中国互联网协会会员