记Oracle中regexp_substr函数的一次调优(速度提高99.5%)

项目中需要做一个船舶代理费的功能，针对代理的船进行收费，那么该功能的第一步便是选择进行代理费用信息的录入，在进行船舶选择的时候，发现加载相关船舶信息十分的慢，其主要在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%；页面秒开，爽。