笼统的说,在一个查询加上where之后,返回的数据量会大大减少(特列不说了)。
第二条语句中的字查询的结果明显比第一条字查询的少,比较次数也少了很多(相当多),所以速度快啦!
第二条语句中的字查询的结果明显比第一条字查询的少,比较次数也少了很多(相当多),所以速度快啦!
解决方案 »
- 求每个月第几周是那几天的SQL语句写法
- record is locked by another user
- LPAD函数问题
- 如何把日期转换成这样的串:MM/DD/YYYY HH:MI:SS AM/PM eg. 12/14/2005 12:50:54 AM
- 尝试加载 Oracle 客户端库时引发 BadImageFormatException。如果在安装 32 位 Oracle 客户端组件的情况下以 64 位模式运
- 触发器~~如何得到DML语句影响行数``````
- 求和问题
- 简单的JDBC classpath设置的问题 在线等 解决问题后 分不是问题
- 如何实现4个表的查询?假如一个表中没有数据该如何?
- sql*plus登陆不进去了!!
- oracle9.0不认识orcal 8.0导出来的脚本的格式,为什么?
- 9i如何缩小UNODTBD?
第一条语句:最多比较次数1600*1550,最少比较数据1600
第二条语句:最多比较次数1600*子查询得出的数据量,最少1600所以写SQL的时候,能够加where的尽量加,能加在最内层的where尽量加在最内层!
用in
1:已用时间: 00: 00: 18.07;2:已用时间: 00: 00: 01.04
用exit
1:已用时间: 00: 00: 17.07;2:已用时间: 00: 00: 01.07
用in
2:已用时间: 00: 00: 01.07
用exit
2:已用时间: 00: 00: 01.04
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then
joined to the original table -- typically.
As opposed to select * from t1 where exists ( select null from t2 where y = x )That is processed more like:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loopIt always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).
So, when is where exists appropriate and in appropriate?Lets say the result of the subquery
( select y from T2 )is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is very very fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.
Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.
If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors. -----------------------------------Tom
这句执行要多少时间?
问题中有一个地方我写错了。实际上第一条语句快,在执行时。
在我的数据库上exists比in慢。而且Key_id是主键。
不明白!请高手解疑呀!
谢谢!!
难道大家都不看sql 的执行计划的 sqlplus 下的 set autotrace on 不是很难的
而起exist 和in 又不相同 如shahand(死磕)所说