比较以下语句那个更快一点.

zrf8866

acctno为20位数字帐号
1:
select acctno from table_1 where acctno[1,4] in ('3390','3391')

2:
select acctno from table_1 where acctno[1,4]='3390' or acctno[1.4]='3391'


或者有更好的方法吗?

wenlq

用了表做set explain on ;
以下是输出

accno 有索引的话
1.
Estimated Cost: 5
Estimated # of Rows Returned: 2

2.
Estimated Cost: 9
Estimated # of Rows Returned: 2

下面的
index Path  
Lower Index Filter 都一样。
1) mytab: INDEX PATH

    (1) Index Keys:acctno
        Lower Index Filter: mytab.acctno[1,5] = '12345'

    (2) Index Keys: acctno
        Lower Index Filter: mytab.acctno[1,5] = '12346'

没索引时
1.
Estimated Cost: 296098
Estimated # of Rows Returned: 244601

1) mytab: SEQUENTIAL SCAN

    Filters: mytab.acctno[1,5] IN ('12345' , '12346' )


2.
Estimated Cost: 296098
Estimated # of Rows Returned: 244601

1) mytab: SEQUENTIAL SCAN

    Filters: (mytab.acctno[1,5] = '12345' OR mytab.acctno[1,5]='12346' )

Informix FAQ 里说 尽可能避免 or 条件， or 条件比 in 要慢些。
or 条件可以转化为 两个条件 的union ,用union 更快。

http://www.iiug.org/resources/faq/ifaq05.htm.1#5.1.4

[ 本帖最后由 wenlq 于 2006-9-6 17:13 编辑 ]