解讀Oracle中代替like進行模糊查詢的方法instr(更高效)

2022-11-21 14:02:27

一、簡介

相信大家都使用過like進行模糊匹配查詢，在oracle中，instr()方法可以用來代替like進行模糊查詢，巨量資料量的時候效率更高。

本文將對instr()的基本使用方法進行詳解以及通過範例講解與like的效率對比。

二、使用說明

instr(sourceString,destString,start,appearPosition)

對應引數描述

instr（'源字串' , '目標字串' ,'開始位置','第幾次出現'），返回目標字串在源字串中的位置。

後面兩個引數可要可不要。

我們以一些範例講解使用方法

【a】從開頭開始查詢第一個‘h’出現的位置

--從開頭開始查詢第一個‘h'出現的位置
select instr('zhangsan', 'h') as idx from dual; --2

查詢結果：

【b】從開頭開始查詢‘an’在字串中的位置

--從開頭開始查詢‘an'在字串中的位置
select instr('zhangsan','an') idx from dual; --3

查詢結果：

【c】從第一個位置開始查詢，返回第二次出現‘a’的位置

--從第一個位置開始查詢，返回第二次出現‘a'的位置
select instr('zhangsan','a',1,'2') idx from dual; --7

查詢結果：

【d】從倒數第一個位置開始，查詢第一次出現‘a’的位置

--從倒數第一個位置開始，查詢第一次出現‘a'的位置
select instr('zhangsan','a',-1,1) idx from dual;  --7

查詢結果：

【e】從倒數第一個位置開始，返回第二次出現‘a’的位置

--從倒數第一個位置開始，返回第二次出現‘a'的位置
select instr('zhangsan','a',-1,2) idx from dual;   --3

查詢結果：

三、instr()與like比較

instr函數也有三種情況

a. instr(欄位,'關鍵字') > 0 相當於欄位like '%關鍵字%': 表示在字串中包含‘關鍵字’
b. instr(欄位,'關鍵字') = 1 相當於欄位like '關鍵字%' 表示以‘關鍵字’開頭的字串
c. instr(欄位,'關鍵字') = 0 相當於欄位not like '%關鍵字%' 表示在字串中不包含‘關鍵字’

下面通過一個範例說明like 與 instr()的使用比較

【a】使用like進行模糊查詢

with temp1 as (
select 'zhangsan' as name from dual),
temp2 as (
select 'zhangsi' as name from dual),
temp3 as (
select 'xiaoming' as name from dual),
temp4 as (
select 'xiaohong' as name from dual),
temp5 as (
select 'zhaoliu' as name from dual)
 
select * from (select * from temp1 
union all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union all
 
select * from temp5) res where res.name like '%zhang%'

查詢字串中包含‘zhang’的結果：

【b】使用instr()進行模糊查詢

(1) 查詢字串中包含‘zhang’的結果：

with temp1 as (
select 'zhangsan' as name from dual),
temp2 as (
select 'zhangsi' as name from dual),
temp3 as (
select 'xiaoming' as name from dual),
temp4 as (
select 'xiaohong' as name from dual),
temp5 as (
select 'zhaoliu' as name from dual)
 
select * from (select * from temp1 
union all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union all
 
 
select * from temp5) res where instr(res.name,'zhang') > 0;

(2) 查詢字串中不包含‘zhang’的結果：

with temp1 as (
select 'zhangsan' as name from dual),
temp2 as (
select 'zhangsi' as name from dual),
temp3 as (
select 'xiaoming' as name from dual),
temp4 as (
select 'xiaohong' as name from dual),
temp5 as (
select 'zhaoliu' as name from dual)
 
select * from (select * from temp1 
union all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union all
 
select * from temp5) res where instr(res.name,'zhang') = 0;

(3) 查詢以‘zhang’開頭的字串：

with temp1 as (
select 'zhangsan' as name from dual),
temp2 as (
select 'zhangsi' as name from dual),
temp3 as (
select 'sizhangsan' as name from dual),
temp4 as (
select 'xiaohong' as name from dual),
temp5 as (
select 'zhaoliu' as name from dual)
 
select * from (select * from temp1 
union all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union all
 
 
select * from temp5) res where instr(res.name,'zhang') = 1;

(4)instr與like特殊用法

select id, name from users where instr('a, b', id) > 0;
--等價於
select id, name
  from users
 where id = a
    or id = b;
--等價於
select id, name from users where id in (a, b);

四、效率對比

【a】使用plsql建立一張十萬條資料測試資料表，同時為需要進行模糊查詢的列增加索引

--建立10萬條測試資料
create table test_instr_like as 
select rownum as id,'zhangsan' as name 
from dual
connect by level <= 100000;
 
--name列建立索引
create index idx_tb_name on test_instr_like(name);

【b】使用like進行模糊查詢

select * from TEST_INSTR_LIKE t where t.name like '%zhang%'

總耗時： 60秒

【c】使用instr進行模糊查詢

select * from TEST_INSTR_LIKE t where instr(t.name, 'zhang') > 0;

總耗時：50秒

由圖可見，instr查詢的速度確實比like快一些，但是，看執行計劃的話，instr卻比like耗時一點。如下圖：

五、總結

以上是對instr基本使用方法的講解以及通過範例對比了like與instr的效率，在進行模糊查詢的時候，能用instr的話就儘量用instr,畢竟資料量大的時候還是有一點優勢的，本文是筆者對like以及instr的一些總結和見解，僅供大家學習參考，也希望大家多多支援it145.com。

解讀Oracle中代替like進行模糊查詢的方法instr(更高效)

目錄

一、簡介

二、使用說明

對應引數描述

我們以一些範例講解使用方法

三、instr()與like比較

instr函數也有三種情況

下面通過一個範例說明like 與 instr()的使用比較

四、效率對比

五、總結

熱門文章