https://www.modb.pro/db/1717179181560324096 --转载 Oracle 批量更新(BULK)优化技巧
面对一个需要更新大量数据的任务,我平时的处理方法是通过循环,每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:集合变量(联合数组等),用来存放查到的结果