Updating large volume of records

Question

I have a table having more than 10 mil records and I have to update multiple columns of this table from the data which I get from few other tables which again have large number of records. All the tables are indexed

I tried doing it in batches and updating in concurrent sessions but it it taking very long time. Is there any alternative approach for this problem?

Check out this comment by John Bittner on AskTom: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6407993912330 — Roberto Navarro, Apr 01 '14 at 16:21
I did try one of the above approaches and as I multiple columns to be updated from different tables it is still taking very long time to create the table. — chapter473, Apr 02 '14 at 13:20
I find interesting that creating the table is taking a long time, that operation should be almost instant. Are you creating the table like this "create table xyz_HOLD as select * from xyz where rownum<1"? If so, are you then altering like this 'Alter tablexyz nologging'? Also, please note the hints that they're using.. — Roberto Navarro, Apr 02 '14 at 18:40

score 0 · Accepted Answer · answered Aug 04 '16 at 08:57

Solution is to build a brand new table(with nologging) and to parallelize.

1) First create your dummy hold table (with no rows):

create table xyz_HOLD as select * from xyz where rownum<1

Avoid logging all these operations*...

Alter table xyz_HOLD nologging

*With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table

2)

    insert /*+ append parallel (xyzhold,12) */ 
      into xyz_hold xyzhold (field1, field2, field3) 
    select /*+ parallel (x,12) */ xyz.field1, my_new_value_for_field2, xyz.field3 
      from xyz x 
     where blah blah blah

3) when done, either rename the table, or swap the partition if your original table is partitioned, and you only updated one partition as we do.

+) Obviously you also need to rebuild indecies, etc as required afterwards.

Updating large volume of records

1 Answers1