How best to shift periodic column data in a VLDB

Problem:
You have a VLDB with a number of tables that require a weekend batch process to shift data. These tables have amount columns (WK1, WK2, ...or MTH_AMT1, MTH_AMT2, MTH_AMT3...) for the periods they cover. As periods change, you need to roll off the first column and move all the other ones over one. (i.e., AMT2's value will be places in AMT1, WK2 will move to WK1, etc.). Don't worry about the last column. It's not essential to our problem.

Considerations:
There are millions of records in some of the involved tables -- this will be an involved process
This process will occur weekly and monthly -- it can't take all weekend You don't want to fragment your tablespaces with the update -- your disk space is large, but not unlimited

Your choices are:
Updating the table in place, fragmentation and speed be damned. Create a new table based on a select of the original table, and reapply all indexes, constraints, triggers, synonyms, and grants as necessary. Some other unmentioned method

What is the preferred method?

BTW, I am thinking create a new table for each then reapplying indexes, etc. after the fact.

Thanks,
Kurt Received on Wed Feb 09 2000 - 18:32:35 CST