Re: How best to shift periodic column data in a VLDB

Split the table vertically into:

    Amount table
    Reference table
Set up a one to one meaningless key.
Create a join view to let users see the data

Create new amounts table as
select from old amounts table.

various other options exist - but it
would help if you mentioned the
version of Oracle.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Kurt Johnson wrote in message ...
>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 - 17:23:34 CST