处理大数据量的存储过程

Question

现在又一个500w数据量的记录需要更新，我写了个存储过程，
使用游标逐条更新，加了个计数，每3000条commit一次并将计数归零。
但是执行起来还是很慢，敢问各位有没有什么更好的办法，或者分析一下我这样做有什么问题。open cursor in select ……接下来就是循环处理了。close cursor；
不胜感激涕零。

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

Answer 2

TYPE V_TAB IS TABLE OF ...

Answer 3

谢谢，你的意思是每次取出3k条记录放入一个相当于临时表的东西，然后每处理3k条commit；TYPE V_TAB IS TABLE OF ...
这个table of后面的东西写什么呢？我存储过程写的不多……

Answer 4

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

Answer 5

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

Answer 6

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;这样写行么？

Answer 7

给你一个我练习的例子！
declaretype 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);beginopen v_cursor;loopfetch 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 loopselect 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;
/

Answer 8

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

Answer 9

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;

Answer 10

FORALL 循环体只能是一条DML语句，比如 INSERT, UPDATE, 或 DELETE我就是每次只update一条记录

Answer 11

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;
这个是啥意思，我不需要二进制整形数，我查询出来的结果是字符串，怎么写？

Answer 12

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

Answer 13

临时写的一个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;

Answer 14

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