you are not comparing slow by slow to bulk
you are comparing an in memory array to fetching data from a table???
I don't know what you are doing here.
bad - slow by slow - fetch a row, process a row
less worse - bulk - fetch N rows, process N rows
good - set based - do it in a single sql
bad:
create or replace procedure slow_by_slow
as
begin
for x in (select rowid rid, object_name
from t t_slow_by_slow)
loop
x.object_name := substr(x.object_name,2)
||substr(x.object_name,1,1);
update t
set object_name = x.object_name
where rowid = x.rid;
end loop;
end;
less worse:
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;
good:
update t set object_name = substr(object_name,2) || substr(object_name,1,1)
table T is just a copy of all objects.