[PT]EXIST versus IN

zz from 蓝精灵

oracle中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 如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。 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 这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。 综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。 Description in SQL TUNING(OU) In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, use IN. If the selective predicate is in the parent query, use EXISTS. Sometimes, Oracle can rewrite a subquery when used with an IN clause to take advantage of selectivity specified in the subquery. This is most beneficial when the most selective filter appears in the subquery and there are indexes on the join columns. Conversely, using EXISTS is beneficial when the most selective filter is in the parent query. This allows the selective predicates in the parent query to be applied before filtering the rows against the EXISTS criteria.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8558093/viewspace-1022195/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8558093/viewspace-1022195/