CODEWITHSUNDEEP
oracle11g
prabhu
prabhu

Reputation: 929

Bulk update of column values of entire table

We have an Oracle 11g database table with around 35 million rows. We are in a situation where we have to update all values of one column. This column is indexed.

I have a script that can generate the updated values and can populate it in a text file.

I'm looking for a good strategy to do a bulk update to this table. We can afford a downtime of around 10 hours.

Will it be a good idea to

What are the pitfalls that one can encounter?

I'm not competent in PL/SQL. Is there a way to solve this in PL/SQL or any way "within" the database itself?

Thanks, Prabhu

Upvotes: 0

Views: 1091

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132750

The fastest way will probably be to create an external table based on your flat file of update values and then:

create table new_table as
  select o.col1, o.col2, o.col3, ..., x.value as colN
  from old_table o
  join extern_table x on ...;

(Make sure that join returns all the rows from old_table. The join may need to be an outer join.)

-- drop foreign key constraints that reference old_table
alter table child1 drop constraint fk_to_old_table1;
...

drop table old_table;

rename new_table to old_table;

-- Re-create indexes and constraints on old_table
alter table old_table add constraint oldpk primary key (col1);
...

-- Re-create the dropped foreign key constraints to old_table
alter table child1 add constraint fk_to_old_table1 ...;
...

Upvotes: 1

Related Questions