Efficient way to update all rows in a table

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.

As Marcelo suggests:

UPDATE mytableSET 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 mytableSET new_column = <expr containing old_column>WHERE new_column IS NULLAND 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.

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 => 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 < 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

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