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的执行效率。