Efficient way to update all rows in a table

Question

Hi,

I have a table with a lot of records (could be more than 500 000 or 1 000 000). I added a new column in this table and I need to fill a value for every row in the column, using the corresponding row value of another column in this table.

I tried to use separate transactions for selecting every next chunk of 100 records and update the value for them, but still this takes hours to update all records in Oracle10 for example.

What is the most efficient way to do this in SQL, without using some dialect-specific features, so it works everywhere (Oracle, MSSQL, MySQL, PostGre etc.)?

ADDITIONAL INFO: There are no calculated fields. There are indexes. Used generated SQL statements which update the table row by row.

Answer 1

The usual way is to use UPDATE:

UPDATE mytable
   SET new_column = <expr containing old_column>

You should be able to do this is a single transaction.

Answer 2

You could drop any indexes on the table, then do your insert, and then recreate the indexes.

Answer 3

Might not work you for, but a technique I've used a couple times in the past for similar circumstances.

created updated_{table_name}, then select insert into this table in batches. Once finished, and this hinges on Oracle ( which I don't know or use ) supporting the ability to rename tables in an atomic fashion. updated_{table_name} becomes {table_name} while {table_name} becomes original_{table_name}.

Last time I had to do this was for a heavily indexed table with several million rows that absolutely positively could not be locked for the duration needed to make some serious changes to it.

Answer 4

As Marcelos suggests:

UPDATE mytable
SET new_column = <expr containing old_column>;

If this takes too long and fails due to "snapshot too old" errors (e.g. if the expression queries another highly-active table), and if the new value for the column is always NOT NULL, you could update the table in batches:

UPDATE mytable
SET new_column = <expr containing old_column>
WHERE new_column IS NULL
AND ROWNUM <= 100000;

Just run this statement, COMMIT, then run it again; rinse, repeat until it reports "0 rows updated". It'll take longer but each update is less likely to fail.

Answer 5

What is the database version? Check out virtual columns in 11g:

Adding Columns with a Default Value http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-schemamanagement.html