like查询替换方式

在Oracle 中一遇到模糊查询，我们就会不由自主地想到 LIKE 关键字，一般情况下都是屡试不爽；

但是如果你的数据量很大，就不得不考虑查询效率的问题，这个时候如果用 LIKE 关键字的话可能

效率就非常低，你会不由自主地去思考用更有效率的方法去代替 LIKE，不知道你有没有

找出更好的方法。

 

下面是个人拙见（有更好方法的朋友可以共享一下心得）：

 

我的办法是用 oracle 函数 INSTR()来代替 LIKE 关键字。

具体举例如下：

比如我们有一张号码表 TEL 表，其中有字段 TELNUM ,  假如表中有十几万条数据，

我们要查询以158开头的号码的信息：我们肯定会不约而同的想到

SELECT * FROM TEL WHERE TELNUM LIKE '158%';

毫无疑问这个sql是正确的，但是，你会发现我们的查询速度似乎很慢，遇到这种情况总是让人感到很憋屈，怎么办？下面是我的办法：用函数 INSTR代替 LIKE 如下：

SELECT * FROM TEL WHERE INSTR(TELNUM, '158') = 1;

什么意思呢？，没用过这个函数的可能不太知道什么意思，下面说一下 INSTR()函数的作用

INSTR(参数1，参数2)其实是一个查找字符串的函数，返回的是字符串查找的位置，它有两个参数都是字符类型，如果找到则返回参数2在参数1中的位置，如果没有找到则返回 0。

例如：

SELECT INSTR('abcde', 'a') FROM DUAL; 

--------------------------------------------

1

--------------------------------------------

结果分析，因为a在abcde的第一个位置，所以返回1

 

 

SELECT INSTR('abcde', 'f') FROM DUAL; 

 

--------------------------------------------

0

--------------------------------------------

 

 结果分析，因为f在abcde串中找不到，所以返回0

 

注:

　　instr(title,’手册’)>0 相当于like

　　instr(title,’手册’)=0 相当于not like

 

下面看看我们刚才写的那个sql

SELECT * FROM TEL WHERE INSTR(TELNUM, '158') = 1;

这个能看明白了吧，这就是说，我们在TELNUM中查找 '158'，如果在第一个位置就找到了，那么就查询出这条数据，这样就和 LIKE 一个效果了。

 

 

 

 

 

 

 

 

 

 

 

 

 

在查询时经常使用like作为关键字进行模糊查询,在生产环境中由于数据量较大,使用like查询时比较慢,尝试了一些方法,最终发现使用Oracle的instr函数可以显著提高查询的效率.

关于数据量:

 

SELECT count(1) FROM t_partner_role; 

--612565

SELECT count(1) FROM T_VENDOR_REPOSITORY; 

 

--1178044  

 

sql语句查询时间比较:

 

SELECT count(id)

  from (select t.id

          FROM T_VENDOR_REPOSITORY t

         WHERE t.deleted = 0

           and t.partnerid in (select distinct (tpr.partnerid)

                                 from t_partner_role tpr

                                where tpr.partnerroletype in (1)

                                  and tpr.deleted = 0)

           and t.VENDORNAME like '%拓维信息系统股份有限公司%');

--使用like查询耗时 1.570ms

 

 

 

SELECT count(id)
  from (select t.id
          FROM T_VENDOR_REPOSITORY t
         WHERE t.deleted = 0
           and t.partnerid in (select distinct (tpr.partnerid)
                                 from t_partner_role tpr
                                where tpr.partnerroletype in (1)
                                  and tpr.deleted = 0)
           and instr(t.VENDORNAME,'拓维信息系统股份有限公司',1,1)!=0);

--使用instr查询耗时 0.654ms

 

通过真实数据的比较发现使用instr的查询效率要高于like,以后再优化like查询时可以考虑.

 

 

 

 

 

 

 

 

 

 

使用内部函数instr，可代替传统的like方式查询，并且速度更快。

           instr函数，第一个参数是字段，第二个参数是要查询的串，返回串的位置，第一个是1，如果没找到就是0.

 

           实例：

 

[sql] view plain copy

1. SELECT  
2.         o.user_name  
3. FROM  
4.     sys_user o  
5. WHERE  
6.     INSTR(o.user_name,'主任')>0  

        查找用户名称中包含主任的用户，作用类似于like ‘%主任%’

 

 

 

 

 

 

 

http://blog.csdn.net/lk_blog/article/details/47112209

http://blog.csdn.net/u013492963/article/details/24382191

https://www.cnblogs.com/dshore123/p/7813230.html

https://blog.csdn.net/lanmuhhh2015/article/details/79216804

https://blog.csdn.net/hzhsan/article/details/9186637

https://www.cnblogs.com/dshore123/p/7813230.html