instr和like的效率(oracle)

instr和like的效率比较

测试环境 oracle10g，tcurrentbilllog表总数有近200万条

instr(‘源字符串’,’查询字符串’)>0 相当于 ‘源字符串’ like ’%查询字符串%’ =true

instr(‘源字符串’,’查询字符串’)=0 相当于 ‘源字符串’ like ’%查询字符串%’ =false

1.%a%方式：
select * from pub_yh_bm t where instr(t.chr_bmdm,'2')>0
等份于：
select * from pub_yh_bm t where t.chr_bmdm like '%2%'

2.%a方式：
select * from pub_yh_bm t
where instr(t.chr_bmdm,'110101')=length(t.chr_bmdm)-length('110101')+1
等份于：
select * from pub_yh_bm t where t.chr_bmdm like '%110101'

3.a%方式：
select * from pub_yh_bm t where instr(t.chr_bmdm,'11010101')=1
等份于：
select * from pub_yh_bm t where t.chr_bmdm like '11010101%'

 

l  全表查询

select * from tcurrentbilllog t whereinstr(t.callerno,'0301')>0;
用时：0.188秒

解释计划

SELECT STATEMENT, GOAL = ALL_ROWS              耗费=4187 基数=30833    字节=6659928

 TABLE ACCESS FULL 对象所有者=ICD     对象名称=TCURRENTBILLLOG    耗费=4187 基数=30833    字节=6659928

select * from tcurrentbilllog t where t.callerno like'%0301%';

用时：0.266秒

解释计划

SELECT STATEMENT, GOAL =ALL_ROWS              耗费=4182 基数=30833    字节=6659928

 TABLE ACCESS FULL 对象所有者=ICD     对象名称=TCURRENTBILLLOG    耗费=4182 基数=30833    字节=6659928

结果：instr查询用时少，但解释计划中耗费高一些。

 

l  集合查询

select count(*) fromtcurrentbilllog t where instr(t.callerno,'0301')>0;
用时：3.765秒

解释计划：

SELECT STATEMENT, GOAL = ALL_ROWS              耗费=4183 基数=1   字节=12

 SORT AGGREGATE                  基数=1   字节=12

  TABLE ACCESS FULL    对象所有者=ICD     对象名称=TCURRENTBILLLOG    耗费=4183 基数=30833    字节=369996

select count(*) fromtcurrentbilllog t where t.callerno like '%0301%';

用时：4.157秒

解释计划：

SELECT STATEMENT, GOAL = ALL_ROWS              耗费=4178 基数=1   字节=12

 SORT AGGREGATE                  基数=1   字节=12

  TABLE ACCESS FULL    对象所有者=ICD     对象名称=TCURRENTBILLLOG    耗费=4178 基数=30833    字节=369996

结果：instr查询用时少，但解释计划中开销要高一些。

 

like有时可以用到索引，例如：name like '李%'

而当下面的情况时索引会失效：name like '%李'

 

简单测试来看，instr的效率是比like要高些（orace对内建函数做了优化），而且使用like时，一些索引是不能用的，但oracle支持函数索引，如果使用函数索引的话，执行更快。

 

一般的数据库中，instr和like的效率是没有多大差别的，但对于oracle数据库可以通过函数索引来提高instr的执行效率。