需求简化描述:
Oralce 一条数据按照特定格式拆分成多行数据
如:1;2;3
拆分成:
1
2
3
源数据如图:
最开始我是这么写的
SELECT DISTINCT
REGEXP_SUBSTR( hscbzx, '[^;]+', 1, ROWNUM )
FROM
yszx
WHERE
id = '"+yszxId+"'
connect by LEVEL <=regexp_count(hscbzx, ';') + 1
执行速度惨不忍睹,直接创造纪录,去网上查了下,解释说是regexp_substr regexp_count ,都使用了正则,正则功能强大,但效率很低,然后还用了connect by 递归查询~,检查了下sql,把connect by 后面的regexp_count 优化了下,如下
改成了这样
SELECT DISTINCT
REGEXP_SUBSTR( hscbzx, '[^;]+', 1, ROWNUM )
FROM
yszx
WHERE
id = '"+yszxId+"'
connect by LEVEL <=LENGTH( hscbzx ) - LENGTH( REPLACE ( hscbzx, ';', '' ) ) + 1
去掉了一个使用正则的函数,执行速度提升了一些,但是效果并不明显,然后把connect by 递归优化又优化了下(借鉴网上查到的大神优化的),个人理解是借助dual,把多层递归优化成了单层???有懂的哥们请指教,感激不尽
又改成了这样
SELECT DISTINCT
REGEXP_SUBSTR( hscbzx, '[^;]+', 1, ROWNUM )
FROM
yszx,
( SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 100 ) b
WHERE
id = '"+yszxId+"'
AND 1 <= LENGTH( hscbzx ) - LENGTH( REPLACE ( hscbzx, ';', '' ) ) + 1
执行结果如下,闪电般迅速,飘柔般丝滑,缺点是会产生空白记录~,使用distinct 去重后还是有一条多余的,很烦,暂时还没想到好的办法。