oracle regexp_like like性能测试及优化

来源：互联网发布：ps软件反应慢编辑：程序博客网时间：2024/06/09 18:42

下面是对like和regexp_like的性能测试：

select t.* from ipmsds.META_DT_MSG_RECORD t where t.msg_content like '%DATA.CALLREC.GSM_TCH_DISTRI.SM_APP%' and t.receive_time_stamp between to_timestamp('201606161200','yyyymmddhh24mi') and  to_timestamp('201606162300','yyyymmddhh24mi');

用时9.297s 8.954s 8.094s 9.782s

select t.* from ipmsds.META_DT_MSG_RECORD t where regexp_like (t.msg_content,'DATA.CALLREC.GSM_TCH_DISTRI.SM_APP') and t.receive_time_stamp between to_timestamp('201606161200','yyyymmddhh24mi') and  to_timestamp('201606162300','yyyymmddhh24mi');

用时8.594s 9.781s 9.703s 8.781s

select t.* from ipmsds.META_DT_MSG_RECORD t where regexp_like (t.msg_content,'^.*DATA.CALLREC.GSM_TCH_DISTRI.SM_APP.*$') and t.receive_time_stamp between to_timestamp('201606161200','yyyymmddhh24mi') and  to_timestamp('201606162300','yyyymmddhh24mi');

用时9.297s 9.781s 10.625s 11.324s

select t.* from ipmsds.META_DT_MSG_RECORD t where regexp_like (t.msg_content,'.*DATA.CALLREC.GSM_TCH_DISTRI.SM_APP.*') and t.receive_time_stamp between to_timestamp('201606161200','yyyymmddhh24mi') and  to_timestamp('201606162300','yyyymmddhh24mi')

查了半天都没有查出来，不知道是不是正则有问题，要不然就是效率太低了。

由上可知，如果能满足条件，like的效率还是挺高的。用regexp_like()查询时，正则表达式的写法对性能影响很大。
又比较了下like和regexp_like还是like效率更高些。

0 0