使用数据库的原生内置函数提升sql函数的效率

发布时间:2020-06-09编辑:脚本学堂
使用数据库的原生内置函数提升sql函数的效率

案例:在字符串类型字段中 求目标子串出现的次数(在oracle 9i下测试)
1.建测试数据。建表test_tbl(含字符串类型的字段content),插入10W条记录。
  

复制代码 代码如下:
--建表     
   create table test_tbl (id number,content varchar2(1000));
   --插入10W条测试记录
   begin
      dbms_random.seed(12345678);
      for i in 1 .. 100000
      loop
        insert into test_tbl values(i, dbms_random.string('L',dbms_random.value(10,1000)));
      end loop;
      commit;
    end;

2.创建自定义函数一(采用循环截子串的方式实现,如果截到的子串等于目标子串则出现次数加1。)
 

复制代码 代码如下:
create or replace function f1(str_pattern in varchar2,str_mother in varchar2)
   return number
   is
    i number := 1;
    cnt number := 0;
    len_pattern number := length(str_pattern);
    len_mother number := length(str_mother);
   begin
    while(i <= len_mother)
      loop
        if(substr(str_mother, i ,len_pattern) = str_pattern) then
          cnt := cnt + 1;
          i := i+ len_pattern;
         else
         i := i+1;
        end if;
      end loop;
      return cnt;
    end;

3.创建自定义函数二(循环使用Instr函数利用occurrence参数实现,循环一次Intr函数得到一个位置pos值,并让occurrence加1。如果pos值不为零,表示目标子串第occurrence次在母串中存在;反之表示再也不存在了,于是退出循环。最后occurrence-1即为所得)
    Intr函数说明:Instr(string, substring, position, occurrence) 其中
        string:代表源字符串;
        substring:代表想聪源字符串中查找的子串;
        position:代表查找的开始位置,该参数可选的,默认为 1;
       occurrence:代表想从源字符中查找出第几次出现的substring,该参数也是可选的,默认为1;
       返回值为:查找到的字符串的位置。
 

复制代码 代码如下:
create or replace function f2(str_pattern in varchar2,str_mother in varchar2)
  return number
  is
    pos number;
    occurrence number := 1;
   begin
      loop
         pos := instr(str_mother,str_pattern,1, occurrence);
         exit when pos = 0;
         occurrence := occurrence + 1;
       end loop;
       return occurrence - 1;
      end;       

3.测试效率                 
 select count(*) from test_tbl  where f1('abc',content) > 1
--用时59.223 S
select count(*) from test_tbl  where f2('abc',content) > 1
--用时2.016 S
select count(*) from test_tbl  where f1('a',content) > 10
--用时59.453 S
select count(*) from test_tbl  where f2('a',content) > 10
--用时8.36 S

4.总结:很好的利用内置函数,科学的把内置函数放到合理的位置能很好的提高效率

5.后记
对于该案例,如果目标子串含有多个字符用f2效率较高,如果目标子串所含字符很少比如就一个字符,建议用简便办法:1.在母字符串中把木目标字串替换为空('')得到新字符串。2.利用 (母串长度-新串长度)/目标子串长度 得到的就是子串的出现次数。