informix exists与in效率测试记录.docVIP

Exists与in的效率测试记录 目的： 找出何种情况适用in还是exists条件为最优。 环境描述： 操作系统：linux 数据库：IBM Informix Dynamic Server Version 10.00.UC3R1 表： Chen建表模式 create table informix.chen ( a integer, b integer, c char(10) ) extent size 16 next size 16 lock mode page; revoke all on informix.chen from public; create index informix.chenona on informix.chen (a) using btree in datadbs ; create index informix.testf on informix.chen (informix.testfunction(b)) using btree in datadbs ; create table informix.test ( a integer, b integer, c integer ) extent size 16 next size 16 lock mode page; revoke all on informix.test from public; create index testona on test(a); Sql语句： 语句in: Select * from chen where a in (select a from test); 语句exists : Select * from chen where exists (select a from test where test.a=chen.a); 数据库为有日志缓存模式 测试过程： A、外表比内表大只差别在数据量不同测试： 1、小量数据 Chen记录为10000 Test 记录为1000 结果输出: 语句in: QUERY: Select * from chen where a in (select a from test) Estimated Cost: 87 Estimated # of Rows Returned: 1000 1) informix.chen: INDEX PATH (1) Index Keys: a (Serial, fragments: ALL) Lower Index Filter: informix.chen.a = ANY subquery Subquery: Estimated Cost: 38 Estimated # of Rows Returned: 1000 1) informix.test: SEQUENTIAL SCAN 语句exists QUERY: Select * from chen where exists (select a from test where test.a=chen.a) Estimated Cost: 666 Estimated # of Rows Returned: 1000 1) informix.test: INDEX PATH (Skip Duplicate) (1) Index Keys: a (Key-Only) (Serial, fragments: ALL) 2) informix.chen: INDEX PATH (1) Index Keys: a (Serial, fragments: ALL) Lower Index Filter: informix.test.a = informix.chen.a NESTED LOOP JOIN 因为记录量太少没必要做时间的测试。 结论： In的成本更低 2、数据量加大十倍第一次 Chen记录为100000 Test 记录为10000 测试输出 语句in: QUERY: Select * from chen where a in (select a from test) Estimated Cost: 809 Estimated # of Rows Returned: 10000 1) informix.chen: INDEX PATH (1) Index Keys: a (Serial, fragments: ALL) Lower Index Fi