This is a secret that is not taught at Oracle
University, a trick known to all DBAs who spend late nights, weekends
and holidays performing database maintenance during tight windows of
When you are updating the majority of rows in a
table, using Create Table as Select (CTAS) is often more efficient
performance than a standard update. The CTAS method employs this
The database is offline and a full backup
will be done after the table is modified.
All indexes and constraints are dropped and
rebuilt after the table update.
The decode clause is used to change the data
when the table is copied. (e.g. select decode (status,'new','old',status
The CTAS method uses the nologging
and parallel method to speed up the table copy.
After the table is copied and re-named, all
indexes and constraints are re-created in nologging and
The CTAS method is designed for massive updates,
and it is used by DBAs during batch windows when the database is in
maintenance mode and no other operations are being done against the
The CTAS method is fastest when the vast
majority of the table rows are effected. As a general rule of thumb,
any update that effects more than half the table rows may see faster
performance with a CTAS update.
For example, assume that the
following update changes 75% of our table rows:
status = 'new'
status = 'old’;
In this case, a parallelized CTAS may perform far
faster (Note: Make sure that you have an SMP server before using the
parallel degree option):
select /*+ full parallel(mytab,35)*/
col2, col3, col4
-- rebuild indexes, triggers and constraints to new_mytab
rename mytab to bkup_mytab;
rename new_mytab to mytab;
In cases where the updates are part of a
scheduled weekend batch, this technique can reduce a large table
update dramatically saving precious hours during scheduled