September 12, 2015

FORALL Far Faster than FOR

Here is a quick example of three approaches to a single task: take the rowid and EVENT_TYPE from a sample table, run the UPPER() function on EVENT_TYPE, and then update the original, all in a bulk collect loop with 100 rows fetched.

Generating sample data:

create table aa_jeff_test as select event_type,foo from large_table where pk1 between 3707322626 - 100000 and 3707322626;

Table created.

SQL> select count(1) from aa_jeff_test;

COUNT(1)
----------
100001

The first program, SLOWEST_DEMO , uses BULK COLLECT with FOR and commits ever row.

11:32:16 SQL> exec slowest_demo;

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.73
11:32:32 SQL> exec slowest_demo;

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.70


The second, SLOW_DEMO, uses BULK COLLECT with FOR and commits every batch of 100 rows. This significantly reduces the amount of work the database needs to do.
11:32:44 SQL> exec slow_demo;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.61
11:32:53 SQL> exec slow_demo;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.19

The third, FAST_DEMO, uses BULK COLLECT with FORALL and similarly commits every batch of 100 rows

11:32:58 SQL> exec fast_demo;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.30
11:33:03 SQL> exec fast_demo;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.54

Thatís a big difference! FORALL is more than twice as fast here as FOR loop with a similar batch commit cycle, and five times as fast as a FOR loop committing every row.

Code is as follows:

create or replace procedure slowest_demo as

type rowidARRAY is table of rowid;
type eventARRAY is table of aa_jeff_test.EVENT_TYPE%type;

my_rowids rowidARRAY;
my_events eventARRAY;
i number;

cursor c is select rowid,event_type from aa_jeff_test;

begin
open c;
loop
fetch c bulk collect into my_rowids,my_events limit 100;
EXIT WHEN my_rowids.COUNT = 0;
for i in 1..my_rowids.count
LOOP
update aa_jeff_test set event_type=upper(my_events(i))
where rowid=my_rowids(i);
commit;
end loop; --for
end loop; --bulk
close c;
end;
/
create or replace procedure slow_demo as

type rowidARRAY is table of rowid;
type eventARRAY is table of aa_jeff_test.EVENT_TYPE%type;

my_rowids rowidARRAY;
my_events eventARRAY;
i number;

cursor c is select rowid,event_type from aa_jeff_test;

begin
open c;
loop
fetch c bulk collect into my_rowids,my_events limit 100;
EXIT WHEN my_rowids.COUNT = 0;
for i in 1..my_rowids.count
LOOP
update aa_jeff_test set event_type=upper(my_events(i))
where rowid=my_rowids(i);
end loop; --for
commit;
end loop; --bulk
close c;
end;
/

create or replace procedure fast_demo
as
type rowidARRAY is table of rowid;
type eventARRAY is table of aa_jeff_test.EVENT_TYPE%type;

my_rowids rowidARRAY;
my_events eventARRAY;
i number;

cursor c is select rowid,event_type from aa_jeff_test;

begin
open c;
loop
fetch c bulk collect into my_rowids,my_events limit 100;
forall i in 1..my_rowids.count
update aa_jeff_test set event_type=upper(my_events(i))
where rowid=my_rowids(i);
commit;
exit when c%notfound;
end loop;
close c;
end;
/

Yes, I selected the EVENT_TYPE just to show off using two different
variables in this procedure, using parallelism would be faster,
ditto not committing until all the work is done, etc.

Posted by jeffreyb at 12:25 AM | Comments (0)