This might seem to be a basic question. But I i thought its always better ask for expert advice before doing something important.
I have a table to be updated in Production, exactly 5 really large tables . We have to update approximately 300,000 records in each table ( 2 years of data for 6 category CODE). The tables are partitioned by week number.
I am not going with create a fresh table add the data and rename, and delete the original method.I am really scared of the impact and the table contains total records of 44,000,000 Records .
I am planning to update the partition for each category code. That is , to update data for an year will result 318 update statements .(3 weeks and 6 category code will result 53*6 =318).
Hence the number of records to be updated by each update statement is reducing to around approximately 10,000 records.
My doubt is how to give a production Script for this.
1. Give 635 update statements ( For 2 years of data) one by one. Commit after each update statements.
2. Create a PL/SQL script to select the partition dynamically and update statement to update . Something like this
FOR i IN 1 .. <<number of weekly partitions>>
LOOP
UPDATE cdr_data cdt
SET a = 'B'
WHERE ab = 'c'
AND partition_key = <<minimum date>> + i and catcode =1 and division=54;
commit;
END LOOP;
Thanks in advance and let me know if you think any other method which works better.