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