oracle 优化之批量处理bulk correct 和 forall
世风之狡诈多端，到底忠厚人颠扑不破； 末俗以繁华相尚，终觉冷淡处趣味弥长。
BULK COLLECT运用
在游标中运用
declare
cursor C_CUR is SELECT * FROM T_TEST;
TYPE T_TYPE IS TABLE OF T_TEST%ROWTYPE;----需要定义一个数据记录的类型
C_REC T_TYPE;
begin
open C_CUR ;
loop
FETCH C_CUR BULK COLLECT INTO C_REC LIMIT 5000;
EXIT WHEN C_REC.COUNT=0; ---这个地方不可以用 exit when C_CUR%notfound 会导致少记录 如果用的话可以在循环结束的时候用,这是区别于普通游标的一个关键地方
for I in C_REC.first..C_REC.LAST
LOOP
INSERT INTO MXQ(ID,NAME) VALUES (C_REC(I).ID,C_REC(I).NAME);
UPDATE MXQ SET ID=C_REC(I).ID WHERE NAME=C_REC(I).NAME;
END LOOP;
EXIT WHEN C_CUR%NOTFOUND;--粉色标明的地方 可以任意选择一个退出循环
end loop;
close C_CUR;
end;
forall 运用
forall也是一个集合提取 它不同于for循环的地方在于
1.它不是一个循环 不用接loop 和 end loop
2.forall 后面只能跟一个dml语句.否则会报错.
综合运用BULK CORRECT 和 forall之后以上sql核心部分可以改写成这样
loop
FETCH C_CUR BULK COLLECT INTO C_REC LIMIT 5000;
EXIT WHEN C_REC.COUNT=0;
forall I in C_REC.first..C_REC.LAST
INSERT INTO MXQ(ID,NAME) VALUES (C_REC(I).ID,C_REC(I).NAME);----forall 后面只能跟一个dml语句
forall I in C_REC.first..C_REC.LAST
UPDATE MXQ SET ID=C_REC(I).ID WHERE NAME=C_REC(I).NAME;
end loop;
关于fetch bulk collect into 游标记录类型的声明
声明一个表中的记录
DECLARE
CURSOR C_CUR IS SELECT * FROM TAB_TEST;
TYPE C_TYPE IS TABLE OF TAB_TEST%ROWTYPE;
C_REC C_TYPE;
声明多个表中的记录
DECLARE
CURSOR C_CUR IS SELECT A.ROWID,B.NAME FROM TAB_TEST_1 A,TAB_TEST_2 B WHERE A.ID=B.ID;
TYPE C_TYPE_REC IS RECORD (ROWID VARCHAR2(32),
NAME TAB_TEST_2%TYPE );----因为是两个表中的字段所以需要声明一个记录类型record包含这两个表的字段
TYPE C_TYPE IS TABLE OF C_TYPE_REC;----把类型声明成刚才那个记录的类型, 注意不需要%ROWTYPE 因为C_TYPE_REC已经是一个记录类型了
C_REC C_TYPE;
测试 效率 数据270万 环境11g
用BULK COLLECT 和 forall
写法一
DECLARE
CURSOR C_CUR IS SELECT A.ROWID FROM mxq A;
TYPE C_TYPE IS TABLE OF C_CUR%ROWTYPE;
C_REC C_TYPE;
BEGIN
OPEN C_CUR;
LOOP
FETCH C_CUR BULK COLLECT INTO C_REC LIMIT 10000;
EXIT WHEN C_REC.count=0;
FORALL I IN C_REC.FIRST..C_REC.LAST
UPDATE mxq A SET A.BEIZHU=(SELECT BEIZHU FROM mxq_1 B WHERE A.SNAME=B.SNAME ) WHERE A.ROWID=C_REC(I).ROWID;
commit;
END LOOP;
CLOSE C_CUR;
END; 222秒
写法二
DECLARE
CURSOR C_CUR IS SELECT A.ROWID,B.BEIZHU FROM mxq A,mxq_1 B WHERE A.SNAME=B.SNAME;
TYPE C_TYPE IS TABLE OF C_CUR%ROWTYPE;
C_REC C_TYPE;
BEGIN
OPEN C_CUR;
LOOP
FETCH C_CUR BULK COLLECT INTO C_REC LIMIT 10000;
EXIT WHEN C_REC.count=0;
FORALL I IN C_REC.FIRST..C_REC.LAST
UPDATE mxq A SET A.BEIZHU=C_REC(I).BEIZHU WHERE A.ROWID=C_REC(I).ROWID;
commit;
END LOOP;
CLOSE C_CUR;
END; 267秒
这两个写法加载进游标的数据记录不一样经过测试发现 差距不大.
用两种写法分别使用批处理和不使用批处理
不用BULK COLLECT 和 forall
写法一
DECLARE
CURSOR C_CUR IS SELECT A.ROWID FROM mxq A;
C_REC C_CUR%ROWTYPE;
BEGIN
OPEN C_CUR;
LOOP
FETCH C_CUR INTO C_REC ;
EXIT WHEN C_CUR%NOTFOUND;
UPDATE mxq A SET A.BEIZHU=(SELECT BEIZHU FROM mxq_1 B WHERE A.SNAME=B.SNAME ) WHERE A.ROWID=C_REC.ROWID;
commit;
END LOOP;
CLOSE C_CUR;
END;1000秒
写法二
DECLARE
CURSOR C_CUR IS SELECT A.ROWID,B.BEIZHU FROM mxq A,mxq_1 B WHERE A.SNAME=B.SNAME;
C_REC C_CUR%ROWTYPE;
BEGIN
OPEN C_CUR;
LOOP
FETCH C_CUR INTO C_REC ;
EXIT WHEN C_CUR%NOTFOUND;
UPDATE mxq A SET A.BEIZHU=C_REC.BEIZHU WHERE A.ROWID=C_REC.ROWID;
commit;
END LOOP;
CLOSE C_CUR;
END;804秒
不用批处理的dml语句 写法二比写法一快的明显一些快了200秒.
总结
1.批处理比单条处理快了大概4倍.
2.sql写法也一定程度上决定了dml的速度.
3可以用并发来提高dml速度,可以看我之前对并发做的测试.
最后送看文章的小伙伴一句话
乾坤未定,你我皆是黑马.
