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值能被返回。