Updating millions of records in oracle
I get this question often, and the answer is: do not use UPDATE (or DELETE if you are purging instead of updating).
UPDATE and DELETE are great in online transaction processing (OLTP) systems in which you are modifying a few records (such as 1 or 10 at a time).
So you’ll write into the UNDO segment for each and every row with the additional bookkeeping information you track in the UNDO segment, meaning that you’ll probably put twice as many or more UNDO blocks into the UNDO segment as you have table blocks.
You bypass the buffer cache for the modifications—they go straight to disk—and there’s no competition with other sessions for blocks in the cache.
You perform significantly less I/O overall and use less CPU time (much less, typically), because you don’t have to maintain the data structure that is the buffer cache.
To view some testaments to this approach, check out bit.ly/WDML5Y.
“How to Update Millions of Records in a Table” is a question that was first asked more than a decade ago and whose answer has grown over the years, the last update to it being just a few days ago. I want to update and commit for a certain number of records—say 10,000—but I don’t want to do it in one stroke, because I may end up with rollback segment issues.