For those of you that may not know, PL/SQL has what is normally an enhancement that results in a full commit being performed at the end of a job, rather than when the programmer instructs it do so. The reason it does this is that the purpose of commits are to allow the user to confirm the change has actually been persisted to disk, so they can move on. Since the PL/SQL block never returns to the caller until the completion of the block, why worry about committing? It’s actually really creative.
In my case, I wanted to progressively apply the changes to 100’s of millions of rows, rather than wait for the end. As such, I committed every 10,000 rows, but found no updates were actually performed.
The rows to update required complex logic. It got into a “funky” state later in the loop, when it stopped processing rows and started reading undo. I’m pretty sure this was related to updating some rows that I was also reading in the loop. I ended up rewriting the thing in python, committing when I wanted, and all was well.
When you can, write a single SQL statement, as it is always faster. However, if you have a need to see periodic progress, (ignoring) commits inside a PL/SQL loop may rear up and bite you in the backside.