Efficient way to update all rows in a table

Question

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

User · Answer

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

User · Answer

The usual way is to use UPDATE   UPDATE mytable    SET new column    lt expr containing old column gt    You should be able to do this is a single transaction

User · Answer

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

User · Answer

As Marcelo suggests   UPDATE mytable SET new column    lt expr containing old column gt     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    lt expr containing old column gt  WHERE new column IS NULL AND ROWNUM  lt   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   EDIT   A better alternative that should be more efficient is to use the DBMS PARALLEL EXECUTE API   Sample code  from Oracle docs    DECLARE   l sql stmt VARCHAR2 1000     l try NUMBER    l status NUMBER  BEGIN    -- Create the TASK   DBMS PARALLEL EXECUTE CREATE TASK   mytask       -- Chunk the table by ROWID   DBMS PARALLEL EXECUTE CREATE CHUNKS BY ROWID  mytask    HR    EMPLOYEES   true  100      -- Execute the DML in parallel   l sql stmt     update EMPLOYEES e        SET e salary   e salary   10       WHERE rowid BETWEEN  start id AND  end id     DBMS PARALLEL EXECUTE RUN TASK  mytask   l sql stmt  DBMS SQL NATIVE                                   parallel level   gt  10      -- If there is an error  RESUME it for at most 2 times    l try    0    l status    DBMS PARALLEL EXECUTE TASK STATUS  mytask      WHILE l try  lt  2 and l status    DBMS PARALLEL EXECUTE FINISHED     LOOP     l try    l try   1      DBMS PARALLEL EXECUTE RESUME TASK  mytask        l status    DBMS PARALLEL EXECUTE TASK STATUS  mytask      END LOOP     -- Done with processing  drop the task   DBMS PARALLEL EXECUTE DROP TASK  mytask     END      Oracle Docs  https   docs oracle com database 121 ARPLS d parallel ex htm ARPLS67333

User · Answer

update Hotels set Discount 30 where Hotelid    1 and Hotelid  lt   5504

User · Answer

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

[sql] Efficient way to update all rows in a table

Examples related to sql

Examples related to performance

Examples related to oracle