Update on Large Table

http://jonathanlewis.wordpress.com/2008/07/28/big-update/
UPDATE is the heaviest DML operation and it will take a long time, regardless of whether you COMMIT after each update operation (for one table) or at end only.



=============

1. create table tabc select * from tabb;

2. truncate table tabb;

2.1 drop indexes from tabb;

3. insert into tabb (new_id,old_id, col1 ..... col 99)
select decode(a.old_id,null,c.new_id,a.new_id) as new_id, old_id, col1
........ col99
from tabc c, (select old_id,new_id from taba group by old_id,
new_id) a
where c.old_id = a.old_id(+);


4. create indexes on tabb;


===========

At a high level…
Create a new table by selecting everything from the old table and performing the calculation in the select clause
Create table TABLE_NEW nologging as select <perform calculation here> from TABLE_OLD;
Apply all constraints, indexes, grants, etc. to the new table
Drop the old table
Rename TABLE_NEW to TABLE_OLD;
Note the nologging option bypasses generating any redo and will result in significant performance improvement.





Use CTAS in lieu of large updates

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.  For example, assume that the following update changed 75% of the table rows:

update
   mytab
set
   status = 'new'
where
   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):

create table new_mytab NOLOGGING as
select  /*+ full parallel(mytab,35)*/
   decode (status,'new','old',status,
   col2, col3, col4
from mytab;

-- rebuild indexes, triggers and constraints to new_mytab

rename mytab to bkup_mytab;
rename new_mytab to mytab;