Mega Code Archive
Compare the performance differences between row-at-a-time processing and bulk processing
<![CDATA[
SQL>
SQL> create table myTable1 as select * from dba_objects where 0=1;
Table created.
SQL> create table myTable2 as select * from dba_objects where 0=1;
Table created.
SQL>
SQL> create or replace procedure row_at_a_time_test as begin
2
3 for x in ( select * from all_objects )
4 loop
5 insert into myTable1 values X;
6 end loop;
7
8 end;
9 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace procedure bulk_test
2 as
3 type array is table of all_objects%rowtype;
4 l_data array;
5 cursor c is select * from all_objects;
6 begin
7
8 open c;
9 loop
10 fetch c bulk collect into l_data LIMIT 100;
11
12 forall i in 1 .. l_data.count
13 insert into myTable2 values l_data(i);
14
15 exit when c%notfound;
16 end loop;
17
18 end;
19 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> exec row_at_a_time_test
PL/SQL procedure successfully completed.
SQL> exec bulk_test
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myTable1;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
]]>