再发新贴：关于oracle存储过程全表扫描处理百万数据的速度问题

Question

今天做数据处理的时候遇到一个问题，就是要把一个表A中的某个字段的数据经过处理以后再插入到表B中。一开始用到的是游标，不过最后发现速度跟本不行，执行半天还没完
A表中有800万数据。
具体的逻辑是这样的，就是A表中有一个字段type，这个type中有多个值，其中是以,号隔开的。比如：010,030,020.
现在的情况是我要把A表中的type字段中的值拿出来然后以,号隔开，变成010 030 020 三个值，然后作为三条记录放到表B的字段type 中（其中ID表为A表中的ID）。在B表中记录会是这样
id type
10030 010
10030 030
10030 020
三条记录。
存储过程是这样
 create or replace procedure TEST is
  TYPE CS IS REF CURSOR;
  v_RESULTSET  CS;
  v_RESULTSET1 CS;
  v_type       varchar2(30);
  v_stype      varchar2(30);
  v_id         varchar2(32);
begin
  OPEN v_RESULTSET FOR
    SELECT a.TYPE, a.ID
      FROM A a;
  LOOP
    FETCH v_RESULTSET
      INTO v_type, v_id;
    exit when v_RESULTSET%NOTFOUND;
    OPEN v_RESULTSET1 FOR
      SELECT * FROM TABLE(CAST(fn_split(v_type, ',') AS ty_str_split));
    LOOP
      FETCH v_RESULTSET1
        INTO v_stype;
      exit when v_RESULTSET1%NOTFOUND;
      insert into B values (v_id, v_stype, '1');
    END LOOP;
    CLOSE v_RESULTSET1;
  END LOOP;
  CLOSE v_RESULTSET;
end TEST;
就是这样，速度异常的慢。不过小北刚接触oracle存储过程，不知道有没有其它的好的实现方式。先谢谢大家了。像上面这个是循环套循环，速度一定会慢的。但是自己实在是想不到有没有更好的解决办法了。希望有经验的大大们不吝赐教！先谢谢了

Answer 1

你这个效率的确有点低，给你个用正则表达式的例子，参考下，我的真实应用用得挺快的，不知道你的如何，如下
select *
from 
(select 1001 as vtr_id,
                                         dbms_lob.substr(regexp_substr('01,020,01510,050,52,98',
                                                                       '[^,]+',
                                                                       1,
                                                                       x.n)) as speed,
                                         rownum rn
                                    from dual a,
                                         (select rownum n
                                            from dual
                                          connect by rownum < 110) x)
                           where speed is not nullVTR_ID SPEED RN
1001 01 1
1001 020 2
1001 01510 3
1001 050 4
1001 52 5
1001 98 6

Answer 2

--有id也是一样小嵌套循环，自己动动手！
CREATE OR REPLACE PROCEDURE TEST IS
  TYPE CS IS REF CURSOR;
  v_RESULTSET  CS;
  v_RESULTSET1 CS;
  v_type       VARCHAR2(30);
  v_stype      VARCHAR2(30);
  v_id         VARCHAR2(32);
BEGIN
  OPEN v_RESULTSET FOR
    SELECT a.TYPE, a.ID FROM A a;
  LOOP
    FETCH v_RESULTSET
      INTO v_type, v_id;
    EXIT WHEN v_RESULTSET%NOTFOUND;
    INSERT INTO B
      SELECT v_id, column_value, '1'
        FROM TABLE(CAST(fn_split(v_type, ',') AS ty_str_split));
  END LOOP;
  CLOSE v_RESULTSET;
END TEST;

Answer 3

--有id也是一样小嵌套循环，自己动动手！
CREATE OR REPLACE PROCEDURE TEST IS
  TYPE CS IS REF CURSOR;
  v_RESULTSET  CS;
  v_RESULTSET1 CS;
  v_type       VARCHAR2(30);
  v_stype      VARCHAR2(30);
  v_id         VARCHAR2(32);
BEGIN
  OPEN v_RESULTSET FOR
    SELECT a.TYPE, a.ID FROM A a;
  LOOP
    FETCH v_RESULTSET
      INTO v_type, v_id;
    EXIT WHEN v_RESULTSET%NOTFOUND;
    INSERT INTO B
      SELECT v_id, column_value, '1'
        FROM TABLE(CAST(fn_split(v_type, ',') AS ty_str_split));
  END LOOP;
  CLOSE v_RESULTSET;
END TEST;

Answer 4

--改进一下,不使用自定义函数fn_split
CREATE OR REPLACE PROCEDURE TEST IS
  TYPE CS IS REF CURSOR;
  v_RESULTSET  CS;
  v_RESULTSET1 CS;
  v_type       VARCHAR2(30);
  v_stype      VARCHAR2(30);
  v_id         VARCHAR2(32);
BEGIN
  OPEN v_RESULTSET FOR
    SELECT a.TYPE, a.ID FROM A a;
  LOOP
    FETCH v_RESULTSET
      INTO v_type, v_id;
    EXIT WHEN v_RESULTSET%NOTFOUND;
    --直接分拆
    INSERT INTO B
    SELECT v_id,str,'1'
      FROM (WITH t AS (SELECT v_type str FROM dual)
             SELECT regexp_substr(str, '[^,]+', 1, LEVEL) str
               FROM t
             CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1);        
  END LOOP;
  CLOSE v_RESULTSET;
END TEST;

Answer 5

SELECT v_id,str,'1'
      FROM (WITH t AS (SELECT v_type str FROM dual)
             SELECT regexp_substr(str, '[^,]+', 1, LEVEL) str
               FROM t
             CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1);        
请问这段什么意思？麻烦能解释一下吗。顺便小北也学习一下存储过程的语法。嘿嘿

Answer 6

(select 1001 as vtr_id,
                                         dbms_lob.substr(regexp_substr('01,020,01510,050,52,98',
                                                                       '[^,]+',
                                                                       1,
                                                                       x.n)) as speed,
                                         rownum rn
                                    from dual a,
                                         (select rownum n
                                            from dual
                                          connect by rownum < 110) x)
请问这段什么意思？麻烦能解释一下吗。顺便小北也学习一下存储过程的语法。嘿嘿

Answer 7

最好的方法是
insert 
项目select
提取你要插入的字段并分拆

Answer 8

begin
for rec in(select type from A)
loop
  insert into B(id,type)
    select level,regexp_substr(rec.type,'[^,]+',1,level)
      from dual
      connect by level <= length(rec.type)-length(replace(rec.type,',',''));
end loop;
commit;
end;---
--例如，例子如此剩下的靠你自己了。[SYS@orcl] SQL>select level, regexp_substr('aaa,bds,asd,234er,er,rfgfg,dsdf','[^,]+',1,level)
  2  from dual
  3  connect by level <= length('aaa,bds,asd,234er,er,rfgfg,dsdf')-length(replace('aaa,bds,asd,234er,er,rfgfg,dsdf',',',''));     LEVEL REGEXP_SUBSTR('AAA,BDS,ASD,234ER,ER,RFGFG,DSDF','[^,]+',1,LEVE
---------- --------------------------------------------------------------
         1 aaa
         2 bds
         3 asd
         4 234er
         5 er
         6 rfgfg已选择6行。

Answer 9

OK！问题解决。总结如下：
大数据量操作存储过程最好避免使用游标。可考虑使用其它方法代替。
另游标的嵌套是存储过程中一大忌。
同时也验证了游标的单层循环其实也不是特别慢。但最好也能找到其它方法代替。通过此贴，小北也学习了不少东西。在这里特别感谢一下
tangren、BenChiM888、java3344520和所有参与的童鞋们！ok!结贴。