处理大数据量的存储过程

现在又一个500w数据量的记录需要更新，我写了个存储过程，
使用游标逐条更新，加了个计数，每3000条commit一次并将计数归零。
但是执行起来还是很慢，敢问各位有没有什么更好的办法，或者分析一下我这样做有什么问题。

open cursor in select ……

接下来就是循环处理了。

close cursor；


不胜感激涕零。

17 个解决方案

#1

在FETCH 时候使用BULK COLLECT 读取批量数据
OPEN T_cur;
FETCH T_cur BULK COLLECT INTO V_TAB LIMIT 3000

更新的时候
使用 FORALL 而不要使用for loop
使用FORALL比FOR效率高，因为FORALL只切换一次上下文，而FOR LOOP将是在循环次数一样多个上下文间切换

FORALL idx IN 1..V_TAB.COUNT
  update....

#2

TYPE V_TAB IS TABLE OF ...

#3

谢谢，你的意思是每次取出3k条记录放入一个相当于临时表的东西，然后每处理3k条commit；

TYPE V_TAB IS TABLE OF ...
这个table of后面的东西写什么呢？

我存储过程写的不多……

#4

可以用BULK COLLECT 
其它注意比如关闭log，删除索引，导入后再重建索引以及约束等

#5

删除索引就算了，我是根据索引来更新的

#6

FORALL idx IN 1..V_TAB.COUNT
  update....
这东西怎么取值？

#7

v_total := 0;
sqlstr := 'select……';
open cur for sqlstr;
fetch cur BLUK COLLECT into V_TAB LIMIT 3000;
FORALL idx IN 1..V_TAB.COUNT;
  v_total := v_total + 1;
  update ……where 字段名 = idx.字段名；
  if(v_total = 3000)then
     commit;
     v_total := 0;
  end if;
close cur;

这样写行么？

#8

给你一个我练习的例子！

declare

type record_table is table of addr_relation_tab%rowtype index by binary_integer;
v_rc record_table;

cursor v_cursor is select * from addr_relation_tab where rownum<=2000000;

v_month  varchar2(4);

begin

open v_cursor;

loop

fetch v_cursor bulk collect into v_rc limit 10000;

/*if(v_rc.count = 0)then 
	exit;--和下面的exit when v_rc.count = 0;等价
end if;
*/
exit when v_rc.count = 0;
for i in v_rc.first..v_rc.last loop

select trunc(DBMS_RANDOM.VALUE(1,12)) into v_month from dual;

if(length(v_month)<2) then
v_month:='0'||v_month;
end if;

execute immediate 'insert into s_message_tab(original_task_id,recv_object,notice_content,month)'||
                   'values(:1,:2,:3,:4)'using v_rc(i).groupid,v_rc(i).mobile,'内容来源'||v_rc(i).mobile,v_month;
commit;

end loop;

exit when v_cursor%notfound;

end loop;

end;
/

#9

我很郁闷，模仿着写了一下，总是报错……

#10

FORALL 循环体只能是一条DML语句，比如 INSERT, UPDATE, 或 DELETE

#11

v_total := 0;
sqlstr := 'select……';
open cur for sqlstr;
fetch cur BULK COLLECT into V_TAB LIMIT 3000;
/*
如果对V_TAB进行操作的话,使用
单独一个for loop 循环操作
UPDate的话使用FORALL,
通过这种方式减少上下文切换次数
*/
FORALL idx IN 1..V_TAB.COUNT;
  update ……where 字段名 = V_TAB.字段名；
close cur;

#12

FORALL 循环体只能是一条DML语句，比如 INSERT, UPDATE, 或 DELETE

我就是每次只update一条记录

#13

TYPE V_TAB IS TABLE OF ...
上面的我试了下，不行……，肯定是我写错了

然后我模仿这样写
type record_table is table of addr_relation_tab%rowtype index by binary_integer;
v_rc record_table;

但是我不知道
addr_relation_tab%rowtype index by binary_integer;
这个是啥意思，我不需要二进制整形数，我查询出来的结果是字符串，怎么写？

#14

引用 13 楼 wokao112358 的回复:

TYPE V_TAB IS TABLE OF ...
上面的我试了下，不行……，肯定是我写错了

然后我模仿这样写
type record_table is table of addr_relation_tab%rowtype index by binary_integer;
v_rc record_table;

但是我不知道
addr_relation_tab%rowtype……

index by binary_integer
是指以数字作为索引表的下标

#15

临时写的一个Demo
可以参考看看
declare
CURSOR empno_cursor IS SELECT EMPNO FROM EMP WHERE DEPTNO = 10;
CURSOR emgr_cursor  IS SELECT MGR   FROM EMP WHERE DEPTNO = 10;

TYPE TB_empno IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
V_TB_empno TB_empno;

TYPE TB_emgr IS TABLE OF EMP.MGR%TYPE INDEX BY BINARY_INTEGER;
V_TB_emgr TB_emgr;

begin
  /*使用BULK COLLECT 取批量数据*/
  OPEN empno_cursor;
  FETCH empno_cursor BULK COLLECT INTO V_TB_empno LIMIT 3000;
  CLOSE empno_cursor;
  /*使用BULK COLLECT 取批量数据*/
  OPEN emgr_cursor;
  FETCH emgr_cursor BULK COLLECT INTO V_TB_emgr LIMIT 3000;
  CLOSE emgr_cursor;
  
  /*对取出的数据的逻辑操作于FOR LOOP 中*/
  FOR i IN 1 .. V_TB_empno.COUNT
  LOOP
    V_TB_emgr(i) := i;
  END LOOP;
  
  /*更新数据库使用FORALL*/
  FORALL idx IN INDICES OF V_TB_emgr
    UPDATE emp SET mgr = V_TB_emgr(idx) WHERE empno = V_TB_empno(idx);
end;

#16

引用 13 楼 wokao112358 的回复:

TYPE V_TAB IS TABLE OF ...
上面的我试了下，不行……，肯定是我写错了

然后我模仿这样写
c

但是我不知道
addr_relation_tab%rowtype ……

如addr_relation_tab表有字段a,b,c,d;则record_table这个记录类型也有a,b,c,d字段(因为addr_relation_tab%rowtype就体现了该点).
定义一个嵌套表类型：record_table，该record的字段是根据addr_relation_tab表的字段进行定义的，index by binary_integer这个是给该嵌套表建立了一个索引。
type record_table is table of addr_relation_tab%rowtype index by binary_integer;
定义一个嵌套表的变量：
v_rc record_table;

#17

问题搞定了，不过用这种方法效率还是不够，500w需要35分钟左右的时间，结贴了，谢谢各位！