The "select * from big where object_id in ( select object_id from small )" will sort BIG once and SMALL once and join them (sort merge join) in all likelyhood.
The "select * from big where exists ( select null from small where small.object_id = big.object_id )" will tend to FULL SCAN big once and FOR EVERY ROW in big -- FULL SCAN small.
(after running it -- i verified it and the "rule of thumb" holds true. BIG outer query and SMALL inner query = IN. SMALL outer query and BIG inner query = WHERE EXISTS. Remember -- thats is a RULE OF THUMB and rules of thumb always have infinitely many exceptions to the rule.
this explanation hold good even for NOT IN and NOT EXISTS ?
No, partially because NOT IN and NOT EXISTS are not substitutes for eachother! They mean semantically different things and can/will return different answers.
See
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:442029737684
链接:http://asktom.oracle.com/pls/asktom/f?p=100:11:1554118810596011::::P11_QUESTION_ID:953229842074
链接:http://asktom.oracle.com/pls/asktom/f?p=100:11:1554118810596011::::P11_QUESTION_ID:953229842074
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23650854/viewspace-690200/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
Oracle使用NFS的mount选项
请登录后发表评论
登录
全部评论
