forall分批提交oracle,oracle 使用BULK COLLECT+FORALL加速批量提交

一、批量提交

批量提交特点:

占用较少undo,资源(独占锁,undo)快速释放,执行时间长

批量提交适合场景:

在线大批量插入,更新,删除数据

二、BULK COLLECT+FORALL性能提升

1.通过BULK COLLECT加速查询

不管是显示游标还是隐式游标，都可以通过BULK COLLECT在数据库的单次交互中获取多行数据。BULKCOLLECT相对Cursor Loop方式减少了PL/SQL引擎和SQL引擎之间的切换次数，因此也减少了提取数据时的额外开销。

2通过LIMIT rows限制提取的记录数

这种方法会减少对PGA的消耗,避免换页产生

3通过FORALL加速DML

FORALL告诉PL/SQL引擎要先把一个或多个集合的所有成员都绑定到SQL语句中，然后再把语句发送给SQL引擎,如果for ..loop循环，那么会发送n(循环的次数)次，而用Forall，一次行全部发送过去。

三、实战

采用bulk collect可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理。

可以在select into,fetch into,returning into语句使用bulk collect。

注意：在使用bulk collect时，所有的into变量都必须是collections

create table t_test as

select object_id, object_name, object_type

from dba_objects

where wner = 'TEST';

1、在select into语句中使用bulk collect

declare

type object_list is table of t_test.object_name%type;

objs object_list;

begin

select object_name bulk collect

into objs

from t_test

where rownum <= 100;

for r in objs.first .. objs.last loop

dbms_output.put_line(' objs(r)=' || objs(r));

end loop;

end;

/

2、在fetch into中使用bulk collect

declare

type objecttab is table of t_test%rowtype;

objs objecttab;

cursor cob is

select object_id, object_name, object_type

from t_test

where rownum <= 10;

begin

open cob;

fetch cob bulk collect

into objs;

close cob;

for r in objs.first .. objs.last loop

dbms_output.put_line(' objs(r)=' || objs(r).object_name);

end loop;

end;

/

以上为把结果集一次fetch到collect中，我们还可以通过limit参数，来分批fetch数据，如下：

declare

type objecttab is table of t_test%rowtype;

objs objecttab;

cursor cob is

select object_id, object_name, object_type

from t_test

where rownum <= 10000;

begin

open cob;

loop

fetch cob bulk collect

into objs limit 1000;

exit when cob%notfound;

dbms_output.put_line('count:' || objs.count || ' first:' || objs.first ||

' last:' || objs.last);

for r in objs.first .. objs.last loop

dbms_output.put_line(' objs(r)=' || objs(r).object_name);

end loop;

end loop;

close cob;

end;

/

你可以根据实际来调整limit参数的大小，来达到最优的性能。limit参数会影响到PGA的使用率。

3、在returning into中使用bulk collect

declare

type id_list is table of t_test.object_id%type;

ids id_list;

type name_list is table of t_test.object_name%type;

names name_list;

begin

delete from t_test

where object_id <= 87510 returning object_id, object_name bulk collect into ids,

names;

dbms_output.put_line('deleted ' || sql%rowcount || ' rows:');

for i in ids.first .. ids.last loop

dbms_output.put_line('object #' || ids(i) || ': ' || names(i));

end loop;

end;

4.用于动态语句

declare

v_query_sql varchar2(500);

type type_emp_table is table of emp%rowtype index by binary_integer;

v_emp_table type_emp_table;

begin

v_query_sql := 'select * from emp';

execute immediate v_query_sql bulk collect into v_emp_table;

forall i in 1..v_emp_table.count

insert into emp_bak values v_emp_table(i);

end;

declare

v_query_sql varchar2(500);

type type_emp_table is table of emp%rowtype index by binary_integer;

v_emp_table type_emp_table;

type type_ename_table is table of emp.ename%type index by binary_integer;

v_ename_table type_ename_table;

begin

update emp set flag=1 where deptno=20 returning flag bulk collect into v_ename_table;

for i in 1..v_ename_table.count loop

dbms_output.put_line(v_ename_table(i));

end loop;

end;