[转]oracle中关于in和exists,not in 和 not existsITeye - 娱乐之横扫全球

[转]oracle中关于in和exists,not in 和 not existsITeye

2019年02月28日11时32分19秒 | 作者: 鸿骞 | 标签: 索引,成果,表面 | 浏览: 2840

in和exists
in 是把表面和内表作hash 衔接,而exists是对表面作loop循环,每次loop循环再对内表进行查询。
not exists:做NL,对子查询先查,有个虚表,有断定值,所以就算子查询有NULL终究也有值回来
not in:做hash,对子查询表树立内存数组,用表面匹配,那子查询要是有NULL那表面没的匹配终究无值回来。

一向以来以为exists比in功率高的说法是不精确的。

假如查询的两个表巨细适当,那么用in和exists不同不大。

假如两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in

例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B)
功率低,用到了A表上cc列的索引
select * from A where exists(select cc from B where cc=A.cc)
功率高,用到了B表上cc列的索引。

相反的
2:
select * from B where cc in (select cc from A)
功率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)
功率低,用到了A表上cc列的索引。

not in 和not exists
假如查询句子运用了not in 那么内表面都进行全表扫描,没有用到索引;
而not extsts 的子查询仍然能用到表上的索引。
所以不管那个表大,用not exists都比not in要快。
一向听到的都是说尽量用exists不要用in,因为exists只判别存在而in需求对比值,所以exists比较快,但看了看网上的一些东西才发现底子不是这么回事。
下面这段是抄的
Select * from T1 where x in ( select y from T2 )
履行的进程适当于:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
select * from t1 where exists ( select null from t2 where y = x )
履行的进程适当于:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

从我的视点来说,in的方法比较直观,exists则有些绕,并且in能够用于各种子查询,而exists如同只用于相关子查询(其他子查询当然也能够用,惋惜没含义)。
因为exists是用loop的方法,所以,循环的次数关于exists影响最大,所以,表面要记载数少,内表就无所谓了,而in用的是hash join,所以内表假如小,整个查询的规模都会很小,假如内表很大,表面假如也很大就很慢了,这时分exists才真实的会快过in的方法。

not in 和not exists
假如查询句子运用了not in 那么内表面都进行全表扫描,没有用到索引;
而not extsts 的子查询仍然能用到表上的索引。
所以不管那个表大,用not exists都比not in要快。
也就是说,in和exists需求具体情况具体剖析,not in和not exists就不必剖析了,尽量用not exists就好了。



典型的衔接类型共有3种:
排序 - - 兼并衔接(Sort Merge Join (SMJ) )
嵌套循环(Nested Loops (NL) )
哈希衔接(Hash Join)

嵌套循环和哈希衔接的算法仍是有不同,在理论上哈希衔接要快过排序和nl,当然实际情况比理论上有杂乱的多,不过两者仍是有差异的.

1 相关子查询与非相关子查询

相关子查询需求在内部引证外部表,而非相关子查询不要引证外部表。关于父查询中处理的记载来说,一个相关子查询是每行核算一次,但是一个非相关子查询只会履行一次,并且成果集被保存在内存中(假如成果集比较小),或许放在一张oracle暂时数据段中(假如成果集比较大)。一个“标量”子查询是一个非相关子查询,回来仅有记载。假如子查询只是回来一个记载,那么oracle优化器会将成果缩减为一个常量,并且这个子查询只会履行一次。

/*select * from emp where deptno in (select deptno from dept where dept_name=admin);*/

2.怎么挑选?

依据外部查询,以及子查询自身所回来的记载的数目。假如两种查询回来的成果是相同的,哪一个功率更好?

相关子查询的体系开支:关于回来到外层查询的记载来说,子查询会每次履行一次。因而,有必要确保任何或许的时分子查询都要运用索引。

非相关子查询的体系开支:子查询只会履行一次,并且成果集一般是排好序的,并保存在暂时数据段中,其间每一个记载在回来时都会被父级查询引证,在子查询回来许多记载的情况下,将这些成果集排序回增大体系的开支。

所以:假如父查询只回来较少的记载,那么再次履行子查询的开支不会非常大,假如回来许多数据行,那么直查询就会履行许屡次。 假如子查询回来较少的记载,那么为内存中保存父查询的成果集的体系开支不会非常大,假如子查询回来多行,那么需求将成果放在暂时段上,然后对数据段排序,以便为负查询中的每个记载效劳。

3定论:
1)在运用一个相关子查询是,运用in 或许 exists子句的子查询履行计划一般都相同

2)exists子句一般不适于子查询

3)在外部查询回来相对较少记载时,相关子查询比非相关子查询履行得要更快。

4)假如子查询中只要少数的记载,则非相关子查询会比相关子查询履行得更快。

4 子查询转化:
子查询能够转化为规范衔接操作

1)运用in的非相关子查询(子查询仅有)

条件:1)在整个层次结构中最底层数据表上界说仅有主键的数据列存在于子查询的select列表中

2)至少有个界说了仅有主键的数据列在select列表中,并且界说仅有主键的其他数据列都有必要有指定的持平规范,不管是直接指定,仍是直接指定。

2)运用exists子句的相关子查询

条件:关于相关条件来说,该子查询只能回来一个记载。

5. not in和not exists调整

1)not in 非相关子查询:转化为in写法下的minus子句

2)not exists相关子查询:这种类型的反衔接操作会为外部查询中每一个记载进行内部查询,除了不满足子查询中where条件的内部数据表以外,他会过滤掉一切记载。

能够重写:在一个等值衔接中指定外部链接条件,然后增加select distinct

eg:select distinct ... from a,b where a.col1 = b.col1(+) and b.col1 is null

6 在子查询中运用all any
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表娱乐之横扫全球立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章