我必须插入表2字段(首先是主键(关于文章),第二个是它们的大小(这些文章).
在源环境中,我有表,主键(TK文章)和大小到第二个字段的串联.但是,我必须插入目标表,TK文章和Artcles的几个大小.
例如,
资源：
ART SIZE** 1 | 28/30 2 | 30/32 3 | Size 10/Size 12/Size 14/Size 14
目标：
ART Size 1 | 28 1 | 30 2 | 30 2 | 32 3 | Size 10 3 | Size 12 3 | Size 14 3 | Size 16
困难在于知道该领域包含多少’/’？
我已经提出了一个问题
SELECT ART,REGEXP_SUBSTR(SIZE,'[^/]+',1,level) FROM TABLLE CONNECT BY REGEXP_SUBSTR(SIZE,level) IS NOT NULL;
选择事务工作并在46秒内显示结果.但是TABLE有100 000行,插入事务太长而且不起作用.
有人可以在这一点上帮助我吗？
谢谢&问候
正则表达式计算起来非常昂贵.如果需要处理大量的行,我个人会使用存储过程 – 管道表函数：
-- table with 100000 rows create table Tb_SplitStr(col1,col2) as select level,'Size 10/Size 12/Size 14/Size 14/Size 15/Size 16/Size 17' from dual connect by level <= 100000
> PL / sql包：
create or replace package Split_Pkg as
type T_StrList is table of varchar2(1000);
function Str_Split(
p_str in varchar2,p_dlm in varchar2
) return T_StrList pipelined;
end;
create or replace package body Split_Pkg as
function Str_Split(
p_str in varchar2,p_dlm in varchar2
) return T_StrList pipelined
is
l_src_str varchar2(1000) default p_str;
l_dlm_pos number;
begin
while l_src_str is not null
loop
l_dlm_pos := instr(l_src_str,p_dlm);
case
when l_dlm_pos = 0
then pipe row (l_src_str);
l_src_str := '';
else pipe row(substr(l_src_str,l_dlm_pos - 1));
l_src_str := substr(l_src_str,l_dlm_pos + 1);
end case;
end loop;
return;
end;
end;
with ocrs(ocr) as(
select level
from ( select max(regexp_count(col2,'[^/]+')) as mx
from tb_splitStr) t
connect by level <= t.mx
)
select count(regexp_substr(s.col2,o.ocr)) as res
from tb_splitStr s
cross join ocrs o
结果：
-- sql with regexp sql> with ocrs(ocr) as( 2 select level 3 from ( select max(regexp_count(col2,'[^/]+')) as mx 4 from tb_splitStr) t 5 connect by level <= t.mx 6 ) 7 select count(regexp_substr(s.col2,o.ocr)) as res 8 from tb_splitStr s 9 cross join ocrs o 10 ; Res ------------------------------ 700000 Executed in 4.093 seconds sql> / Res ------------------------------ 700000 Executed in 3.812 seconds --Query with pipelined table function sql> select count(*) 2 from Tb_SplitStr s 3 cross join table(split_pkg.Str_Split(s.col2,'/')) 4 ; COUNT(*) ---------- 700000 Executed in 2.469 seconds sql> / COUNT(*) ---------- 700000 Executed in 2.406 seconds
