Bulk update in Oracle

When you've millions of records in your system and you want to update rows in a table, it requires to take extra care. There are two options while bulk update
* Update in a single shot - It is faster and easy to do it. But it becomes a high risk when the update statement modifies millions of records (like 10 millions). You'd be stuck with a single update, you'd not be able to see the progress and most of all, your redo log can overflow and other system accessing the database would be heavily slowed down. Your database might even crash in worst case scenario. Below is a sample update query with this approach:

UPDATE [table]
SET [column_name] = 'some value'
WHERE [some clause]


* Update in chunk - It is a slow process but you can overcome the limitations and risks that one shot update might face. The only down side with this approach is it is really a slow process. it could take 10 or 20 times comparing with the first approach. Below is a sample update query with this approach:

SET SERVEROUTPUT ON SIZE 1000000

BEGIN
LOOP
UPDATE [table]
SET [column_name] = 'some value'
WHERE [some clause]
AND ROWNUM <= [how_many_at_a_time];
DBMS_OUTPUT.PUT_LINE('Done xxxx deletion!');
EXIT WHEN SQL%NOTFOUND;
COMMIT;
END LOOP;
END;


Resources:

http://oracle-online-help.blogspot.com/2006/12/delete-in-batches.html - delete in batch