Oracle SQL:具有REGEXP_SUBSTR表达式的插入查询非常长(拆分字符串) [英] Oracle SQL: the insert query with REGEXP_SUBSTR expression is very long ( split string )

问题描述

我必须在表2中插入字段(第一个是关于文章的主键，第二个是关于这些文章的大小).

I must insert into table 2 fields (first the Primary key(about the articles) and the second concerns their size(of these articles).

在源代码环境中，我将主键(TK文章)和大小的连接插入了第二字段.但是，我必须在目标表中插入"TK条款"和若干条款". 例如，

In source envrionnement, i have into table, the primary key(TK Articles) and a concatenation of a size into second field. However, i must insert into target table, the TK Articles and the several size of the Artcles. For example,

来源:

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

困难是要知道该字段中包含多少个"/"?

The difficulty is to know how many '/' is included in the field?

我已经查询

SELECT ART,
       REGEXP_SUBSTR(SIZE,'[^/]+',1,level)
FROM TABLLE
CONNECT BY REGEXP_SUBSTR(SIZE,'[^/]+',1,level) IS NOT NULL;

选择交易有效，并在46秒内显示结果.但是TABLE有10万行，并且插入事务太长，无法正常工作.

the select transaction works and display results in 46 seconds. But the TABLE have 100 000 lines and the insert transaction is too long and doesn't work.

有人可以在这一点上帮助我吗?

Somebody can help me on this point?

感谢&问候

推荐答案

正则表达式的计算成本非常高.如果需要处理大量的行，我个人将使用存储过程-流水线表功能:

Regular expressions are very expensive to compute. If there is a need to process a large number of rows, personally I would go with a stored procedure - pipelined table function:

-- 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 

1. PL/SQL软件包:

1. PL/SQL package:

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, 1, l_dlm_pos - 1));
               l_src_str := substr(l_src_str, l_dlm_pos + 1);
        end case;
     end loop;
     return;
  end; 
end;

带有regexp函数的SQL查询:

SQL Query with regexp functions:

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, '[^/]+', 1, 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, '[^/]+', 1, 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

这篇关于Oracle SQL:具有REGEXP_SUBSTR表达式的插入查询非常长(拆分字符串)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！