(一)in的理解
如sql:Select * from T1 where x in ( select y from T2 )中,先执行select y from T2,等价于Select* from T1,( select distinct y from T2) T2 whereT1.x=T2.y。可以理解为:先进行子查询,结果集合放到缓存中,进行一定的处理(evaluated, distincted, indexed),然后于T1表连接查询,也可以理解为:
定义:优化()={ evaluated,distincted, indexed }
--在缓存中进优化处理,如果集合比较小,优化代价很小,不用每次都到表T2中取值,直接在内存获取,比较快。
List resultSet=[];
Array T1=(select * from T1);
Array T2=(select y from T2);
Array T3=优化(T3)--网上资料很多都没有这一步,这一步其实是 in的优势所在,也是缺陷所在,随着子表的增大,优化的代价越来越高,因为T3的每个y值都要对T1进行全表扫描。
for(int i=0;i<T1.length;i++) {--每次循环,T1全表扫面
for(int j=0;j<T3.length;j++){--如果y为主键时,T3.length等于T2.length,但是在取y值从内存取,速度要比从表快。
if(T1 [i].x ==T3 [j].y) {
resultSet.add(T1 [i]);
break;
}
}
}
return resultSet;
分析:
遍历次数=T3.length*T1.length,in()比较适合用于外查询大于内查询的。建议在外查询建立索引,因为避免每次对T1进行全表扫描。
(二)exists的理解
如sql:Select * from T1 where exists ( select 1 from T2 where y=x )中,可以理解为
for x in(select* from T1)
loop
if (exists ( select 1 from T2 where y=x.x ))
then
(....outputthe record)
end if;
end loop;
---
(1)对T1中每条记录执行exists ( select 1 from T2 where y=x.x )返回值为true或者false。
(2)如果select 1 from T2 where y=x.x查询有存在值则返回true,并把T1中对应的行记录放入结果表(临时表)中;
(3)如果返回false,T1中记录不作处理,指针指向下一条T1中的记录;
(4)重复(1)(2)(3)直到遍历完T1表,输出结果表中的记录。
分析:
T1中有几条记录就需要执行几次select 1 from T2 where y=x.x,如果T1表很大,而且T2没有索引的话需要对T2表进行全表遍历T1.length(T1表中记录数)次,代价好大。如果T1较小,T2有索引则exists效率比较高。
(三)总结
(1)外大于内时用IN,这种说法不恰当,确切的说应该是外查询数量远大于内查询,而且外查询有索引时用IN,数据数量级越大效果越明显。
(2)内大于外用EXISTS也不恰当,应该是内查询数量远大于外查询,同时内查询有索引的有索引的情况下,EXISTS效率要高,数据数量级越大效果越明显。
(3)如果内外数据量接近,IN和EXISTS效率应该看索引、服务器性能(如缓存大小、cpu性能)等情况。
(4)在10g之后的版本,默认都是以基于成本的方式进行,这时候,oracle会先找出可能的执行方式,然后计算出每个执行计划的成本,再选择以较低成本的方式进行计算,这样子在对in和exists的分析中,这两种写法会相互转换,那个统计的成本信息低则会选择那种方式。当然由于oracle的成本信息并不是全量统计得出来的结果,也会有一定的误差,再统计信息是需要人工(或定时)去执行统计的,如果操作大量数据后,没有进行统计,偏差也会很大。