IN和EXISTS分析比较.doc

ORACLE 中IN和EXISTS比较 EXISTS的执行流程? ? ? 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 对于in 和 exists的性能区别: ? 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。 ? 其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ? ? ? ? ? ? ? ? ? ? ? ? ? 另外IN时不对NULL进行处理 如： select 1 from dual where null? in (0,1,2,null) 为空 ? 2.NOT IN 与NOT EXISTS:? ? ? NOT EXISTS的执行流程 select ..... ? from rollup R where not exists ( select Found from title T ? ? ? ? ? ? ? ? ? ? ? ? ? ? where R.source_id = T.Title_ID); 可以理解为: for x in ( select * from rollup ) ? ? ? loop ? ? ? ? ? if ( not exists ( that query ) ) then ? ? ? ? ? ? ? ? OUTPUT ? ? ? ? ? end if; ? ? ? end; 注意:NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。 例如下面语句，看他们的区别： select x,y from t; x? ? ? ? ? ? ? y ------? ? ? ? ------ 1? ? ? ? ? ? ? 3 3? ? ? ? 1 1? ? ? ? 2 1? ? ? ? 1 3? ? ? ? 1 5 select * from t where? x not in (select y from t t2? ) no rows ? ? ? select * from t where? not exists (select null from t t2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? where t2.y=t.x ) x? ? ? y ------? ------ 5? ? ? NULL 所以要具体需求来决定 对于not in 和 not exists的性能区别： ? not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join. ? 如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null NOT IN 在基于成本的应用中较好 比如: select ..... from rollup R where not exists ( select Found from title T ? ? ? ? ? ? ? ? ? ? ? ? ? where R.source_id = T.Title_ID); 改成（佳） select ...... from title T, rollup R where R.source_id = T.Title_id(+) ? ? and T.Title_id is null; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 或者（佳） sql select /*+ HASH_AJ */ ... ? ? ? ? from rollup R ? ? ? ? w