测试
SQL> create table t1 as select * from all_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SQL> create table t2 as select * from t1;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL procedure successfully completed.
--更新方法1,这里只有一次提交
SQL> set timing on
SQL> update t1 set object_name=lower(object_name);
50122 rows updated.
Elapsed: 00:00:01.44
SQL>
--更新方法2:这里使用循环提交了多次
SQL> begin
2 for x in (select rowid rid,object_name,rownum r from t2)
3 loop
4 update t2
5 set object_name=lower(x.object_name)
6 where rowid=x.rid;
7 if(mod(x.r,100)=0) then
8 commit;
9 end if;
10 end loop;
11 commit;
12 end;
13 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.56
SQL>
提交多次的不是想像中的快,反而是慢了不少,如果一条SQL语句能完成那就尽量这么做,而不是把事情复杂化,因这这样做总是更快。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-731279/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-731279/