Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Best approch for bulk update on table

Abhijit GourMay 9 2019 — edited Jun 13 2019

Hi Folks,

We've large table in our ERP database - 190 GB (No partitions), 96 million records, 200 + columns, 30+ indexes. For one time activity, we need to update two columns with one unique value, for about 75 million records; no indexes/constraints on those two columns.

Database - 11.2.0.4 Enterprise edition sized about 5 TB, runs on Exadata platform.

Assuming regular Update SQL may go on for hours/day(s); I could think of couple of alternate ways below -

1.create new copy of table using select * from source table with no records -> insert into new table with parallel hint + nologging and new value of those two columns and pull values from source for rest of columns -> create all indexes/constraints on new table -> drop original table -> rename new table to original -> collect statsĀ  --- Hoping inserting to new table (parallel + nologging) will be faster.

2. add two new temporary columns to original table and insert required values in nologging mode in to the new columns -> drop original columns -> rename new columns to original columns -> rebuild indexes -> collect stats ---- while insert may take a while, but there won't be any constraint on time required, as will be take short outage to rename columns and rebuild indexes.

While few hours of outage for table/database is permitted, I want to see what could be best approach to perform this one time update, any suggestion will be appreciated. Thank you!

-Abhijit

This post has been answered by AndrewSayer on May 9 2019
Jump to Answer
Comments
Post Details
Added on May 9 2019
13 comments
5,831 views