使用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;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27678272/viewspace-2050811/ ，如需转载，请注明出处，否则将追究法律责任。
