the answer is............


it depends.


You will be interested in

http://asktom.oracle.com/pls/asktom/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D6646995323761764246&p_cat=OOW_2009.zip&p_company=822925097021874


the EffPlsql presentation from my OpenWorld talk this year.

slide 31..37 deal with this.


Basically, if you have code like:

For x in (select * from t where …)
Loop
 dbms_output.put_line( … t.x … );
End loop;

you do NOT need to bulk collect - you retrieve data and do something with it and that something does not involve going back to the database with it.

In 10g and above, we implicitly array fetch 100 rows at a time for you when you use implicit cursors like that.


If you have code like this however:

For x in ( select * from t where … )
Loop
         process(x);
 update t set … where …;
End loop;

we do need to bulk it up - the fetch will array fetch 100 records but you'll have 100 single row updates - we want a forall in there!

that code would best be written as:

create or replace procedure bulk
as
    type ridArray is table of rowid;
    type onameArray is table 
             of t.object_name%type;

    cursor c is select rowid rid, object_name 
                  from t t_bulk;
    l_rids      ridArray;
    l_onames    onameArray;
    N           number := 100;
begin
    open c;
    loop
        fetch c bulk collect 
        into l_rids, l_onames limit N;
        for i in 1 .. l_rids.count
        loop
            l_onames(i) := substr(l_onames(i),2)
                        ||substr(l_onames(i),1,1);
        end loop;
        forall i in 1 .. l_rids.count
            update t 
               set object_name = l_onames(i) 
             where rowid = l_rids(i);
        exit when c%notfound;
    end loop;
    close c;
end;

The tkprofs in the ppt show the difference in processing CPU speed. The slow by slow process used 21.25 cpu seconds, the bulk approach used 12.83 seconds.


HOWEVER.....


as I point out in that presentation also.... The best way to write that would be as a single update:

update t set object_name = substr(object_name,2) || substr(object_name,1,1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.30       1.44          0       2166      75736       71825
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.30       1.44          0       2166      75736       71825

if possible!!!