ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Ask Tom "IN & EXISTS"

Ask Tom "IN & EXISTS"

原创 Linux操作系统 作者:zecaro 时间:2011-03-22 22:09:56 0 删除 编辑
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

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

请登录后发表评论 登录
全部评论

注册时间:2010-10-28

  • 博文量
    182
  • 访问量
    356357