Oracle中的in和exists区别

Oracle中的in和exists区别

在SQL查询语句中，经常会很多人对in和exists的查询效率进行疑惑，很多人都认为exists查询速度要比in快，其实这个说话不绝对，exists和in都有它们适合的场合，不然在SQL查询标准中，也不会一直不遗余力的进行支持。

先说in，通常情况下我们即认为是先将in子句里面的内容查询出来，然后对In的查询结果进行合并，再根据查询结果对主SQL进行一个个的查询，即相当于以下转换。
　　select * from T1 where x in (select y from T2);　　
　　可以转换成如下　　
　　select * from T1,(select distinct y from T2) T2　　
　　where T1.x=T2.y;

再说exists，exists即先从主SQL中找到每一条合适的记录，然后将结果放到子SQL中与之匹配，即　　
　　select * from T1 where exists (select NULL from T2 where T2.y=T1.x);　　
　　可以转换成
　　for cursor1 in (select * from T1)　　
　　loop　　
　　if (exists (select NULL from T2 where T2.y=cursor1.x))　　
　　then　　
　　返回记录;　　
　　end if;　　
end loop;

这样子，即我们很容易会认为在子SQL数据量比较大的时候，exists效率会高于in，而只有在in的结果集非常小的时候，in的效率才能比exists好，　这样是否就正确了呢？通过对很多的SQL进行分析，会发现这个准则也不绝对，特别是10g，11g之后的数据库，in和exist很多时候效果都差不多，这又是为什么呢？

再深入分析，Oracle优化器规则中有三种分析规则，以前主要是基于规则的优化器，即通过预先定义一系列的优先级顺序，比如唯一索引优先于普通索引，又或者是等于索引优先于大于索引，这样即按规则的优先顺序去执行SQL，所以我们就有了在子句结果集很小的时候，in查询速度会快于exists，反之则exists速度会更快的结论，在oracle 9i中，默认提供是基于选择的优化器，即当有分析数据时，采用基于成本的优化方式，没有则仍采用基于规则的查询方式，这样优化模式下基本等同于基于规则或者基于成本。在10g之后的版本，默认都是以基于成本的方式进行，这时候，oracle会先找出可能的执行方式，然后计算出每个执行计划的成本，再选择以较低成本的方式进行计算，这样子在对in和exists的分析中，这两种写法会相互转换，那个统计的成本信息低则会选择那种方式。当然由于oracle的成本信息并不是全量统计得出来的结果，也会有一定的误差，再统计信息是需要人工（或定时）去执行统计的，如果操作大量数据后，没有进行统计，偏差也会很大。

这样子的分析是否准确了呢？in和exists又是不是只有这两种查询方式呢？在基于成本的优化方式中，又如何对in和exists进行优化？SQL的优化又是否与具体数据有关？

阅读延伸：关于Oracle中in和exists的区别：