Updating rows with a cursor is slow
Tuning your PL/SQL code starts with tuning the SQL statements you use in that code. If you are using a cursor,...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
that cursor is based on a query. Have you tuned that SQL statement so that it effeciently pulls the data out of the database?
You also mentioned that you are updating rows in another table in your cursor's loop. Are you committing inside that loop? That can be a performance killer. Take the COMMIT out of the loop and see if that helps your performance.
Finally, are you sure that you cannot perform this as one simple SQL statement rather than updating in a cursor's loop? For instance, I might have the following psuedo code:
CURSOR c1 IS SELECT valX FROM tableX; OPEN c1; LOOP FETCH c1 INTO x; EXIT WHEN c1%NOTFOUND; UPDATE tableY SET colA = colA*1.10 WHERE colX = x; END LOOP; CLOSE c1;
UPDATE tableY SET colA = colA*1.1.0 WHERE colX IN (SELECT valX FROM tableX);