forall分批提交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 + FORALL批量提交例句

insert

declare

TYPE ARRAY IS TABLE OF big_table%ROWTYPE;

l_data ARRAY;

CURSOR c IS

SELECT * FROM big_table;

BEGIN

OPEN c;

LOOP

FETCH c BULK COLLECT

INTO l_data LIMIT 5000;

FORALL i IN 1 .. l_data.COUNT

INSERT /*+append*/

INTO big_table

VALUES l_data

(i);

commit;

EXIT WHEN c%NOTFOUND;

END LOOP;

CLOSE c;

delete

DECLARE

CURSOR mycursor IS

SELECT rowid FROM t WHERE OO = XX;

TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;

v_rowid rowid_table_type;

BEGIN

OPEN mycursor;

LOOP

FETCH mycursor BULK COLLECT

INTO v_rowid LIMIT 5000;

EXIT WHEN v_rowid.count = 0;

FORALL i IN v_rowid.FIRST .. v_rowid.LAST

DELETE t WHERE rowid = v_rowid(i);

COMMIT;

END LOOP;

CLOSE mycursor;

END;

update

DECLARE

CURSOR mycursor IS

SELECT t_pk FROM t WHERE OO = XX;

TYPE num_tab_t IS TABLE OF NUMBER(38);

pk_tab NUM_TAB_T;

BEGIN

OPEN mycursor;

LOOP

FETCH mycursor BULK COLLECT

INTO pk_tab LIMIT 5000;

EXIT WHEN pk_tab.count = 0;

FORALL i IN pk_tab.FIRST .. v_rowid.LAST

UPDATE t SET name = name || ’bulk’ WHERE t_pk = pk_tab(i);

COMMIT;

END LOOP;

CLOSE mycursor;

END;