Skip to content

Instantly share code, notes, and snippets.

@tmuth
Created February 13, 2015 22:50
Show Gist options
  • Save tmuth/a811151014b84030cc96 to your computer and use it in GitHub Desktop.
Save tmuth/a811151014b84030cc96 to your computer and use it in GitHub Desktop.
single-row vs array update
-- row by row
for c1 in (select rowid rid, YEAR1,
QUARTER,
...
from flights_400k
)
loop
-- moderately complex PL/SQL logic
update flights_400k
set DEPARTUREDELAYGROUPS = l_group,
DEPDELAYMINUTES = 10
where rowid = c1.rid;
commit;
end loop;
-- array update
open flights_cur;
loop
fetch flights_cur
bulk collect into
l_rowid,l_group,l_delaymin limit l_limit;
exit when l_rowid.count = 0;
for i in l_rowid.first..l_rowid.last
loop
-- do your PL/SQL logic here
l_group(i) := 1;
l_delaymin(i) := 10;
end loop; --i
-- 1 update of the table in bulk
forall j in indices of l_rowid
update /*+ parallel(flights_400k) */ flights_400k
set DEPARTUREDELAYGROUPS = l_group(j),
DEPDELAYMINUTES = l_delaymin(j)
where rowid = l_rowid(j);
dbms_output.put_line('Modified ' || SQL%ROWCOUNT || ' rows');
end loop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment