美文网首页
记Oracle中regexp_substr的一次调优(速度提高9

记Oracle中regexp_substr的一次调优(速度提高9

作者: DuBetter | 来源:发表于2019-12-18 16:49 被阅读0次

    项目中需要做一个船舶代理费的功能,针对代理的船进行收费,那么该功能的第一步便是选择进行代理费用信息的录入,在进行船舶选择的时候,发现加载相关船舶信息十分的慢,其主要在sql语句的执行,因为测试的时候数据较少,实际使用中,数据量较大。

    关于regexp_substr函数的使用可查看Oracle通过一个字段的值将一条记录拆分为多条记录

    需求和表结构

    船舶相关的信息在系统中有船舶动态表(CBDT),另外有一张船舶代理费表(CBDLF),要求对于已经录入代理费的船舶不再出现在列表中(CBDLF表中有记录的需要过滤掉),CBDT中有一个合同清单字段,HTQD,该字段由分号";"拼接多个合同,由于选了船舶,需要计算这个船上所有合同的作业量(拿合同字段和其他表做连接),因此需要切割,方便后继的作业量计算,需求引入就是这里——需要切割合同清单字段(HTQD),存在几个合同,就要将该行变成几条记录。

    • 船舶动态表CBDT(肯定是省略的啦,哪有这么简单的表)
    CBBH HC HTQD
    0001 191210 N20191202-xx;N20191203-xx
    • 船舶动态表CBDLF
    CBBH HC Free
    0001 191210 12534.23

    原来的方案

    对于之前的sql,执行时间长达5秒多,最快也是4秒多,而且是只有一个月的数据。


    原本方案的执行时间

    看看原来的sql语句

    select CBDT.CBBH, CBDT.HC,
      regexp_substr(CBDT.HTBHQD,'[^;]+', 1,LEVEL,'i') HTTDBH
    FROM CBDT 
        WHERE (CBDT.CBBH, CBDT.HC) not IN (SELECT CBBH, HC from CBDLFB)     
        AND KBRQ >= TO_DATE('2019-11-18', 'yyyy-mm-dd') and KBRQ <= TO_DATE('2019-12-18', 'yyyy-mm-dd')
    connect by LEVEL <=regexp_count(CBDT.HTBHQD, ';') + 1
    

    第一次尝试

    使用了not in,显然这满足要求,但事实是not in的效率是十分低下的,(当初在用的时候,我也不知道啊,手动捂脸),所以应该改成join,有了下面的sql

    select CBDT.CBBH, CBDT.HC,
      regexp_substr(CBDT.HTBHQD,'[^;]+', 1,LEVEL,'i') HTTDBH
    FROM CBDT 
    LEFT JOIN CBDLFB ON CBDT.CBBH = CBDLFB.CBBH and CBDT.HC = CBDLFB.HC
        WHERE CBDLFB.CBBH is NULL   
        AND KBRQ >= TO_DATE('2019-11-18', 'yyyy-mm-dd') and KBRQ <= TO_DATE('2019-12-18', 'yyyy-mm-dd')
    connect by LEVEL <=regexp_count(CBDT.HTBHQD, ';') + 1
    

    这样改了之后,基本维持在4秒左右,当然,这还是不能忍的啊。

    第二次尝试

    通过改变时间,无论是延长还是缩短,sql执行的时间基本都在4秒左右,所以,目前的数据量对sql的影响不是很大了,那么肯定是sql本身的问题,去掉regexp_substr后,果然,只需要0.0xx秒的时间,所以基本确定了是这个函数的问题。开始度娘和谷歌。然而只找到了一个百度经验说性能问题,也没有说怎么解决。直到在谷歌上有人说,regexp_substr是正则,其本身效率就不高,不推荐。但是不推荐如前我的需求是必须要用啊(不知道有没有其他方案),找了许久依旧没有解决方案,回头再观察sql,regexp_substr是正则表达式毫无疑问,然后发现最后的regexp_count,这个那应该也是正则,但是regexp_count(CBDT.HTBHQD, ';')的意思是计算有几个分号,这个函数可以换掉啊。所以改用了LENGTH(CBDT.HTBHQD) -LENGTH(REPLACE(CBDT.HTBHQD,';','')) + 1,运行,奇迹发生了。
    新的sql

    select CBDT.CBBH, CBDT.HC,
      regexp_substr(CBDT.HTBHQD,'[^;]+', 1,LEVEL,'i') HTTDBH
    FROM CBDT 
    LEFT JOIN CBDLFB ON CBDT.CBBH = CBDLFB.CBBH and CBDT.HC = CBDLFB.HC
        WHERE CBDLFB.CBBH is NULL   
        AND KBRQ >= TO_DATE('2019-11-18', 'yyyy-mm-dd') and KBRQ <= TO_DATE('2019-12-18', 'yyyy-mm-dd')
    connect by LEVEL <= LENGTH(CBDT.HTBHQD) -LENGTH(REPLACE(CBDT.HTBHQD,';','')) + 1
    
    新的sql执行时间

    速度提到了约:67%。

    1秒多的时间,虽然较原来的5秒要好太多,但是1秒多的卡顿,始终还是不好,那么继续尝试吧。

    找新的方案去了,待更新........(不到1秒内,誓不回);

    ——————————————我是分割线——————————————

    我回来了,因为找到了终极优化,从5秒到0.025s,还是谷歌啊。
    最后执行时间


    最后执行时间

    话不多说,直接看改后的sql

    select CBDT.CBBH, CBDT.HC,
      regexp_substr(CBDT.HTBHQD,'[^;]+', 1,l) HTTDBH -- 原来的LEVEL换成了l,注意
    FROM CBDT 
    LEFT JOIN CBDLFB ON CBDT.CBBH = CBDLFB.CBBH and CBDT.HC = CBDLFB.HC,
        (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b -- 关键
        WHERE CBDLFB.CBBH is NULL   
        AND KBRQ >= TO_DATE('2019-11-18', 'yyyy-mm-dd') and KBRQ <= TO_DATE('2019-12-18', 'yyyy-mm-dd')
       AND l <= LENGTH(CBDT.HTBHQD) -LENGTH(REPLACE(CBDT.HTBHQD,';','')) + 1  
    

    之前的connect 是使用到sql最后,这样的方式会导致数据出现很多冗余,而且冗余特别严重,需要使用distinct,至于原因,还在找。使用regexp_substr函数必须配对使用connect,但是没想到居然可以这样使用。

    5—>0.023 这速度提高99.5%;页面秒开,爽。

    最后

    本文可在我的小站中查看记Oracle中regexp_substr函数的一次调优

    生命不息,使劲造。

    本文内容个人拙见,若有出入,欢迎指正!

    相关文章

      网友评论

          本文标题:记Oracle中regexp_substr的一次调优(速度提高9

          本文链接:https://www.haomeiwen.com/subject/eapunctx.html