Update all rows

oracleupdate

I want to know the most efficient manner to update every row in an extremely large Oracle table for a single column. For example:

update mytable set mycolumn=null;

or:

update mytable set mycolumn=42;

My knowledge may very well be stale. What I do is alter the table to drop the column. Then, I alter the table to add the column with a default value of the new value that I want to use. Then, I alter the table to remove the default value for the column. I find this to be much faster than just running an update, but I have a feeling that there is a better method.

Best Answer

A lot depends on the other activity going on against this table while you are doing this mass update. I hope you have some kind of test environment where you can run some samples of what you'd like to do and get an idea of which way is best. I would try:

  1. Run the single update table set column_name = blah;
  2. Create a plSql loop to select all of the primary keys in the table and loop through them, updating the column=blah and committing every X updates (maybe 10000). You can parallelize this code by copying it and making it copy do a separate section of Primary keys.

We had a very similar issue with a table that was very actively used in the OLTP system and we were able to parallelize it 5x and ran with no user locking impact on a 100+ MM row table committing every 10000. You didn't say how large your table is or what kind of application your are running, but this kind of solution may fit you.

Related Question