【oracle调优】in和exists

In和exists

         相信很多朋友跟我一样，都曾经为这两个关于谁更快的问题纠结过，也曾脑子里不知为什么的记下了一些标准，比如在子表小的情况下in更快，在外表更小的情况下要用exists等。

         但实际你的11g中做下测试，你会感到很奇怪。

         对于两个子表大的情况和外表更大的情况，in和exists会得到相同的执行计划。

在很早的10g，oracle好像就优化了in和exists的区别，两者通常不会再有什么性能方向的差别，因为oracle优化器会自动根据两个表的大小，将in和exists的语句重写为合适的联结。

所以结论就是，在现在的oracle版本中，in和exists在性能上已经基本等效了，不需要在考虑要使用其中一种替换另外一种了。

SEMI JOIN和ANTI JOIN

         在查看in和exists语句的执行计划时，可能经常会看到上面的这两个联结。

         SEMIJOIN半联结

         ANTIJOIN反联结

         半联结的概念：常用于in，exists等产生的执行计划，所以拿in语句来举例。比如

         Select  a.* from a where a.id in (1,3,5,7)

         假设我们上一条查询走的是以a表为驱动的nestloop，则当我们从a表里取出一条记录，然后跟（1,3,5,7）去对比相等。若该记录值为1，则该记录在第一次对比就得到true的结果，则该记录就可以直接返回，而不需要再跟（3,5,7）去做一一的对比。这就相当于只跟列表中的部分值做对比就可以确定结果。所以叫半联结。

         反联结的概念：常用语not in，not exists等产生的执行计划。还拿notin举例

         Select  a.* from a where a.id not in (1,3,5,7)

         当我们从a中取出一条记录，该记录值为1，当我们做第一次对比，发现结果为false，则这条记录直接可以判定是不满足条件的，可以直接舍弃，继续a表的下一条数据对比了。这样的话也跟半联结操作一样，也不需要产生两表记录数乘积的比较运算，因此也能节省成本。这就是反联结。

         需要强调的是，半联结和反联结是一种逻辑的联结方式，跟等值联结，交叉联结等一样，它并不是三大实现方式（nest loop,sort merge,hash）之外的另外一种实现。所以半联结和反联结也是各自有三种实现方式的，比如我可以是hash join anti（哈希反），nest loop anti(嵌套循环反)等。

Not in和not exists

         Notin和not exists在逻辑上并不是完全等效的。所以，在更改写法之前一定要验证逻辑上是否等效，是否可以改写。

         Notin和not exists的不等效主要是因为对null的处理不同。

         首先必须明白，在oracle数据库里，null值不能进行各种算术运算，包括=和<>。判断是否为空的话只能使用IS NULL和IS NOT NULL。

         Select* from tabA where id = null;

         Select* from tabA where id <> null;

         以上两条sql会没有任何返回结果，即使id列有空值。这就是因为null值无法进行等或不等的判断。

         然后就到了not in和not exists的区别了。

         引用一张http://blog.csdn.net/elvis_dataguru/article/details/8426886的图吧。

         通过这张图能很好的理解。

         当使用not in时，无论如何不会返回空值。当子查询中有null值时，没有任何返回结果。

         而not exists因为=判断是在子查询里进行，所以外表的null值能被返回。