Update column value for 500 million rows in spacing table by segment

Question

we have a table with 10 billion rows. This table Interval divided by date . In the department, we need to update the date for 500 million rows that match the criteria for the new value. This will definitely affect the creation of a new partition or something, because the table is split on the same date. Can someone give me pointers to a better approach to follow?

Thanks in advance!

Answer 1

If you are going to update the separation key, and the source lines are in one (optional) section, then a reasonable approach would be as follows:

• Create a temporary table for updated rows. If possible, upgrade on the fly.

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

• Cancel the original (optional) section

   ALTER TABLE original_table DROP PARTITION xxx; 

• Insert updated rows back

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

If you have problems with CTAS or INSERT INTO SELECT for 500M rows, consider splitting the temporary table and moving the data in batches.

Answer 2

hmmm ... If you have enough space, I would create a “copy” of the source table with good updated rows, then check the results and discard the original table after it, at the end rename the “copy” to the source. Yes, this is a long lead time, but it can be a painless way, of course, a parallel hint is needed.

Answer 3

You might consider adding a new “updated” column (Flag) that has a filault value of NULL (or 0, I am preffer NULL) for your table, and using criticism of the dates you need to update, you can update the data group by group in the same way as described by Kombain, after updating a data group, you can affect the value 1 on the “updated” flag in your data group.

As an example, you can start by creating data groups, let them consider that criticism of the groups is the year. so let's start processing the data year after year.

• Create a temporary table of year 1:

CREATE TABLE updated_rows AS SELECT columns... FROM original_table PARITION (2001) WHERE YEAR = 2001 ...;

2. Complete the original (optional) section

ALTER TABLE original_table DROP PARTITION 2001;

3. Insert updated rows back

INSERT /*+append*/ INTO original_table(columns....,updated) SELECT columns...,1 FROM updated_rows;

We hope this helps you process the data step by step to prevent a single update of all the data in the table. You can consider the cursor, which cycle for many years.