oracle 单行拆分多行数据

--正确
with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
  union all
  select 'BR1002' brid, 'BR1002.BR1003' br_level from dual
  union all
  select 'BR1003' brid, 'BR1001.BR1003' br_level from dual
)
select brid,
       level, 
       regexp_count(br_level, '\.') + 1 as row_cnt,
       regexp_substr(br_level, '[^.]+', 1, level) as s_brid
  from z_branch t
connect by level <= regexp_count(br_level, '\.') + 1
   and t.brid = prior t.brid
   and prior dbms_random.value > 0;

借助伪列

with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
  union all
  select 'BR1002' brid, 'BR1002.BR1003' br_level from dual
  union all
  select 'BR1003' brid, 'BR1001.BR1003' br_level from dual
),
z_level as(
  select level lv from dual connect by level < 10
) 
select t.brid,
       a.lv, 
       regexp_count(t.br_level, '\.') + 1 as row_cnt,
       regexp_substr(t.br_level, '[^.]+', 1, a.lv) as s_brid
  from z_branch t
 inner join z_level a
    on a.lv <= regexp_count(t.br_level, '\.') + 1;

--数据量8234
--9.959s rownum<100 9.873s
SELECT sksj,jxdd,SUBSTR(sksj,INSTR(';' || sksj || ';',';',1,RN.RN),INSTR(';' || sksj || ';',';',1,RN.RN+1) - INSTR(';' || sksj || ';',';',1,RN.RN) - 1) 
from T_JWXTZF_JW_JXRW_JXBXXB,(SELECT ROWNUM RN
                  FROM T_JWXTZF_ZFTAL_XTGL_JCSJB
                 WHERE ROWNUM < 10) RN
WHERE LENGTH(sksj) - LENGTH(REPLACE(sksj, ';', '')) + 1 >= RN.RN
and xnm=2023 and sksj is not null

--10.493s   level<=100 15.541s
SELECT sksj,jxdd,SUBSTR(sksj,INSTR(';' || sksj || ';',';',1,RN.RN),INSTR(';' || sksj || ';',';',1,RN.RN+1) - INSTR(';' || sksj || ';',';',1,RN.RN) - 1) 
from T_JWXTZF_JW_JXRW_JXBXXB,(
SELECT LEVEL RN FROM DUAL 
CONNECT BY LEVEL <= 10 )RN
WHERE LENGTH(sksj) - LENGTH(REPLACE(sksj, ';', '')) + 1 >= RN.RN
and xnm=2023 and sksj is not null

--超过100s未响应
SELECT sksj,jxdd,REGEXP_SUBSTR(sksj,'[^;]+',1,LEVEL) value
from T_JWXTZF_JW_JXRW_JXBXXB
where xnm=2023 and sksj is not null
CONNECT BY LEVEL <= REGEXP_COUNT(sksj, '[^;]+')