17,086
社区成员
发帖
与我相关
我的任务
分享关于exists 和in 的效率比较
两张表test1(name, value),test2(name, value)
test1有10000条记录。test2放两条
select *
from test1 T1
where exists(select NAME
from test2
where T1.NAME = test2.NAME)
select *
from test1 T1
where T1.NAME in(select NAME from test2)
明显感觉用in 快，为什么呢，不是说in是最慢的吗？
test1有10000条记录。test2放两条
select *
from test1 T1
where exists(select NAME
from test2
where T1.NAME = test2.NAME)
select *
from test1 T1
where T1.NAME in(select NAME from test2)
明显感觉用in 快，为什么呢，不是说in是最慢的吗？
...全文
请发表友善的回复…
发表回复
jack_4826 2004-09-15
- 打赏
- 举报
这还用比吗，早就得出结论exists快啊
akihide 2004-09-15
- 打赏
- 举报
还是尽量用exists，数据是会不断变多的，等到了上万条的时候再回来返工就不好了
jary12581 2004-09-15
- 打赏
- 举报
exists比in快，但如果update或delete时，用exists可能会出现，回滚段不够的情况
freddy2003 2004-09-15
- 打赏
- 举报
exists快
baojianjun 2004-09-15
- 打赏
- 举报
參考
http://asktom.oracle.com/pls/ask/f?p=4950:8:2063933::NO::F4950_P8_DISPLAYID,F4950_P8_B:953229842074,Y
http://asktom.oracle.com/pls/ask/f?p=4950:8:2063933::NO::F4950_P8_DISPLAYID,F4950_P8_B:953229842074,Y
baojianjun 2004-09-15
- 打赏
- 举报
in & exists(zt)
some example at which situation
IN is better than exist
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
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 loop
It 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.
some example at which situation
IN is better than exist
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
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 loop
It 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.
liuyi8903 2004-09-14
- 打赏
- 举报
我同意dinya2003(OK)的观点。
hai_yu2000 2004-09-14
- 打赏
- 举报
ORACLE会将你的IN优化为exists
freebirdwjy 2004-09-09
- 打赏
- 举报
学习。。。
zhenglou 2004-06-08
- 打赏
- 举报
呵呵，楼上的，弄清出in 和exists还是很有意义的。正如不能因为.net不使用指针而避免去弄指针、
ihexiong 2004-06-07
- 打赏
- 举报
现在，oracle都已经作了相关的优化工作了，甚至能自动将in替换为exists操作，
所以，。。。。
所以，。。。。
HeavenHe 2004-06-07
- 打赏
- 举报
Well, the two are processed very very differently.
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
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 loop
It 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.
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
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 loop
It 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.
dinya2003 2004-06-07
- 打赏
- 举报
看了很多相关的文章,也进行过测试,但是看不出明显的测试效果.但是数据少的时候习惯上用in
crazyCSDNx 2004-05-14
- 打赏
- 举报
在海量数据级上exists超级快，用IN在万级别上可以考虑
skystar99047 2004-04-01
- 打赏
- 举报
exists效率较高
moshangchen 2004-04-01
- 打赏
- 举报
exists 快
在这种情况可以用内连接比较快！
select T1.*
from test1 T1 ,test2 T2
where T1.NAME = test2.NAME)
在这种情况可以用内连接比较快！
select T1.*
from test1 T1 ,test2 T2
where T1.NAME = test2.NAME)
洪十二 2004-04-01
- 打赏
- 举报
exists快
drabit 2004-04-01
- 打赏
- 举报
test2表数据量小的时候是这样的，在test2中放10000条记录试试
baojianjun 2004-04-01
- 打赏
- 举报
用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
低效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
高效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
(译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)
你的情況的出現是由於test2的數據量較小的情況下出現的，
在這種情況下一般不需要區分用哪個比較好，因為不會差很多
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
低效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
高效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
(译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)
你的情況的出現是由於test2的數據量較小的情況下出現的，
在這種情況下一般不需要區分用哪個比較好，因為不會差很多
