关于exists和in的终极比较.docVIP

关于exists 和 in 的 终极比较 .doc 朱思武于2009年2月19日星期四exists 和 in的效率差异。 ----------------------------------------------------------------------------------------------------------------- Oracle版本：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 测试环境： CPU： AMD Athlon(tm) 64 processor 3200+， 内存：2G 测试时间：2009年2月19日----------------------------------------------------------------------- 首先准备一个大表和一个小表： CREATE TABLE bigtab AS SELECT rownum as id, a.* FROM dba_objects a; CREATE TABLE smalltab AS SELECT rownum as id, a.* FROM dba_tables a; 手工处理一下： 把bigtab表设置到1200万左右记录，把smalltab设置到100条记录。 先进行分析： analyze table bigtab compute statistics; analyze table smalltab compute statistics; 在isql*plus执行一组sql： spool f:\result.doc; set autotrace off; set timing off; select count(*) from bigtab a; select count(*) from smalltab b; set autot on; set timing on; --rbo select /*+ rule */ count(*) from bigtab a where a.object_name in (select b.table_name from smalltab b); select /*+ rule */ count(*) from bigtab a where exists (select * from smalltab b where a.object_name=b.table_name); select /*+ rule */ count(*) from bigtab a where exists (select 1 from smalltab b where a.object_name=b.table_name); select /*+ rule */ count(*) from smalltab a where a.table_name in (select b.object_name from bigtab b); select /*+ rule */ count(*) from smalltab a where exists (select * from bigtab b where a.table_name=b.object_name); select /*+ rule */ count(*) from smalltab a where exists (select 1 from bigtab b where a.table_name=b.object_name); --cbo select count(*) from bigtab a where a.object_name in (select b.table_name from smalltab b); select count(*) from bigtab a where exists (select * from smalltab b where a.object_name=b.table_name); select count(*) from bigtab a where exists (select 1 from smalltab b where a.object_name=b.table_name); select count(*) from smalltab a where a.table_name in (select b.object_name from bigtab b); select count(*) from smalltab a where exists (select * from bigtab