Created
February 13, 2015 22:50
-
-
Save tmuth/a811151014b84030cc96 to your computer and use it in GitHub Desktop.
single-row vs array update
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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