Updating millions of records in oracle
This blog post is more a tip that I picked up on while at PASS 2009.
Have you ever had the need to copy the contents of an entire table into another table?
We can make use of an existing column that has an index on it for creating a not null column.
By using an index, the workload can be split in multiple updates. Once the above updates are complete, the column can be marked as not null.
Advantage: time reduced, No structural/system changes are done apart from the column addition, commits are possible at intervals.
Disadvantage: Initial groundwork required for running the updates, explicit coding is required if the updates are to be run in parallel.
A huge DML/DDL activity would take lot of time and would result in space usage problems and heavy resource utilization, hence such batch processing is normally scheduled in off peak hours.The above statement took about 30-35 minutes to execute on my system (excluding statistics generation) with properly sized undo segments.Advantage: No structural/system changes are done apart from the column addition, perfect for tables that are not huge!Below is comparison of some of the methods that are commonly used.