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速度,可以看我之前对并发做的测试.

最后送看文章的小伙伴一句话

乾坤未定,你我皆是黑马.

转载于:https://www.cnblogs.com/throughRiversandLake/p/10894482.html