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

Re-arrange your data so that instead of having a column(s) of data for a given week, you have a set of rows for a given week. Then use the Oracle8 feature of partitioned tables.

In article <yYlo4.16232$vi4.52609_at_dfw-read.news.verio.net>,   "Kurt Johnson" <kujotx_at_hotmail.com> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 09 2000 - 20:39:22 CST