Transactions - Oracle Database Transactions and Locking Revealed - page 93

EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> variable n number
Well, when we go to update, we could simply do it in a single UPDATE statement, like this:
EODA@ORA12CR1> exec :n := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
EODA@ORA12CR1> update t set object_name = lower(object_name);
72614 rows updated.
EODA@ORA12CR1> exec dbms_output.put_line((dbms_utility.get_cpu_time-:n)|| ' cpu hsecs...' );
49 cpu hsecs...
Many people, for whatever reason, feel compelled to do it like this—slow-by-slow/row-by-row—in order to have
a commit every N records:
EODA@ORA12CR1> exec :n := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
EODA@ORA12CR1> begin
2 for x in ( select rowid rid, object_name, rownum r
3 from t )
4 loop
5 update t
6 set object_name = lower(x.object_name)
7 where rowid = x.rid;
8 if ( mod(x.r,100) = 0 ) then
9 commit;
10 end if;
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> exec dbms_output.put_line((dbms_utility.get_cpu_time-:n)||' cpu hsecs...' );
275 cpu hsecs...
In this simple example, it is many times slower to loop in order to commit frequently. If you can do it in a single
SQL statement, do it that way, as it is almost certainly faster. Even if we “optimize” the procedural code, using bulk
processing for the updates (as follows), it is in fact much faster, but still much slower than it could be.
EODA@ORA12CR1> exec :n := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
EODA@ORA12CR1> declare
2 type ridArray is table of rowid;
3 type vcArray is table of t.object_name%type;
4