q1、
终于感觉到distinct的慢,那请问大侠们,怎么才能不用distinct来实现去重的功能那。
例如:select distinct name from student;
q2、
在网上也看了许多资料,都说使用exsits替代in会提高效率,小弟也试着用了用,但对exsits了解还是不好,始终不能很好的运用exsits,希望大侠们能指点一二,
比如
select * from tab1 where exsits(select * from tab2 where tab2.name='XXX');
是不是如果select * from tab2 where tab2.name='XXX'能查出两条记录的话,select * from tab1就会查询两次那?
exsits()中的语句到底是怎么运行的那,到底是怎么回事啊~!~!真是郁闷。
终于感觉到distinct的慢,那请问大侠们,怎么才能不用distinct来实现去重的功能那。
例如:select distinct name from student;
q2、
在网上也看了许多资料,都说使用exsits替代in会提高效率,小弟也试着用了用,但对exsits了解还是不好,始终不能很好的运用exsits,希望大侠们能指点一二,
比如
select * from tab1 where exsits(select * from tab2 where tab2.name='XXX');
是不是如果select * from tab2 where tab2.name='XXX'能查出两条记录的话,select * from tab1就会查询两次那?
exsits()中的语句到底是怎么运行的那,到底是怎么回事啊~!~!真是郁闷。
你写的是不对的。
in select * from tab1 where id in (select id from tab2 where tab2.name='XXX');
exists select * from tab1 where exists (select 1 from tab2 where tab2.name='XXX' and tab1.id=tab2.id);
q2 exists并不是最快的,要看你的具体应用,子查询比较小的情况下用in还是比较快的
先说IN他相当对inner table执行一个个带有distinct的子查询语句,然后得到的查询结果集再与outer table进行连接,当然连接的方式和索引的使用仍然同于普通的两表连接。
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相当于对outer table进行全表扫描,用从中检索到的每一行与inner table做循环匹配输出相应的符合条件的结果,其主要开销是对outer table的全表扫描(full scan),而连接方式是nested loop方式。可以写成
select * from T1 where exists (select NULL from T2 where T2.y=T1.x);
转换成
for cursor1 in (select * from T1)
loopif (exists (select NULL from T2 where T2.y=cursor1.x))
then 返回匹配的记录;
end if;
end loop;
通过上面的解释,现在很容易明白当T2数据量巨大且索引情况不好(大量重复值等),则不宜使用产生对T2的distinct检索而导致系统开支巨大的IN操作,反之当T1表数据量巨大(不受索引影响)而T2表数据较少且索引良好则不宜使用引起T1全表扫描的EXISTS操作。
NOT IN, NOT EXIST:
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
使用表连接连替换EXIST
通常来说,采用表连接的方式比EXISTS更有效率
SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’FROM DEPT
WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT=‘A);
一般下面的写法会更加有效,尤其在DEPT表巨大的时候:
SELECT ENAME FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT=‘A’;
但是毕竟还得具体情况具体解决,因为某些时候增加distinct反而会因为改变oracle的执行计划而提高执行效率,比如下面网页给出的例子:
http://www.68design.net/Development/Database/27375-1.html