Update a column value for 500 million rows in Interval Partitioned table

Question

we've a table with 10 Billion rows. This table is Interval Partitioned on date. In a subpartition we need to update the date for 500 million rows that matches the criteria to a new value. This will definetly affect creation of new partition or something because the table is partitioned on the same date. Could anyone give me pointers to a best approach to follow?

Thanks in advance!

Answer 1

If you are going to update partitioning key and the source rows are in a single (sub)partition, then the reasonable approach would be to:

1. Create a temporary table for the updated rows. If possible, perform the update on the fly

   CREATE TABLE updated_rows
   AS
   SELECT add_months(partition_key, 1), other_columns...
     FROM original_table PARITION (xxx)
    WHERE ...;
   

2. Drop original (sub)partition

   ALTER TABLE original_table DROP PARTITION xxx;
   

3. Reinsert the updated rows back

   INSERT /*+append*/ INTO original_table
   SELECT * FROM updated_rows;
   

In case you have issues with CTAS or INSERT INTO SELECT for 500M rows, consider partitioning the temporary table and moving the data in batches.