面对一个需要更新大量数据的任务，我平时的处理方法是通过循环，每N行提交来完成这个任务。这样做的两个主要原因：
- 1、频繁地提交大量小事务比处理和提交一个大事务更快，也更高效
- 2、没有足够的UNDO空间
今天在学到了一种新的解决思路，在此记录一下方便后面使用。
假设我们有一个表T,行数很多，现在我们想为所有行更新一列的值，操作如下：SQL> set line 800 pagesize 900 -
SQL> set timing on -
SQL> set time on 21:21:24 -
SQL> set serveroutput on 21:06:41 -
SQL> create table t as select * from all_objects; 21:09:36 -
SQL> exec dbms_stats.gather_table_stats(user,'T'); 21:09:59 -
SQL> variable n number; 21:11:35 SQL> exec :n := dbms_utility.get_cpu_time; 21:12:02 SQL> update t set object_name=lower(object_name); 100932 rows updated. 21:21:39 SQL> exec dbms_output.put_line((dbms_utility.get_cpu_time-:n)||' cpu hses...'); 237 cpu hses...- 注：dbms_utility.get_time函数返回当前CPU时间和Wall-clock时间的差值，单位为1/100秒。需要将其转换为毫秒值，即除以10。
示例1：循环+更新100条数据提交（常规逻辑）
begin for x in (select rowid rid,object_name, rownum r from t) loop update t set object_name = lower(x.object_name) where rowid = x.rid; if ( mod(x.r,100)=0) then commit; end if; end loop; commit; end; / PL/SQL procedure successfully completed. Elapsed: 00:00:03.37 21:36:23 SQL> exec dbms_output.put_line((dbms_utility.get_cpu_time-:n)||' cpu hses...'); 336 cpu hses... PL/SQL procedure successfully completed.示例2：优化写法（Bulk Collect）
21:45:22 SQL> exec :n := dbms_utility.get_cpu_time; declare type ridArray is table of rowid; type vcArray is table of t.object_name%type; l_rids ridArray; l_names vcArray; cursor c is select rowid,object_name from t; begin open c; loop fetch c bulk collect into l_rids,l_names limit 100; forall i in 1 .. l_rids.count update t set object_name = lower(l_names(i)) where rowid = l_rids(i); commit; exit when c%notfound; end loop; close c; end; / 21:45:13 -
SQL> exec dbms_output.put_line((dbms_utility.get_cpu_time-:n)||' cpu hses...'); 243 cpu hses...从CPU的消耗中可以看出第二种方法比第一种方法要的很高的性能提升。使用Bulk Collect进行批量检索，会将检索结果一次性绑定到一个集合变量中，而不是通过游标一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT。这些语句大大减少了上下文切换次数（一次切换多次执行），同时提高DML性能。
- 语法如下：
SELECT 字段列表 BULK COLLECT INTO var_collect FROM 表名 WHERE 条件;- ar_collect：集合变量（联合数组等），用来存放查到的结果