Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Large table monthly switch design problem

Re: Large table monthly switch design problem

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 29 Jun 2005 06:49:17 -0700
Message-ID: <1120052963.68713@yasure>


schonlinner_at_yahoo.com wrote:
> Hello,
>
> I'm looking for an idea on how to perform the following
> (Oracle 10g):
>
> We have booking data from the previous 24 months in a large data
> warehouse (OLAP warehouse, customers access it by a java client
> application).
> Each month we do a monthly processing: We add a new month to the
> data and delete the oldest month (so we always have the previous
> 24 months in the database).
>
> The quality check team wants to verify the new warehouse before
> it goes online with the new month.
>
> So we currently are doing the following:
> We copy the fact table and do all changes there. Then we tell
> the quality check team to access this new table to see whether
> all is ok.
>
> If yes, we adjust a public synonym to point to the new fact
> table and delete the old fact table.
>
> The main drawback is, that we need an enourmous amount of
> additional space because for a short time we need the copy of
> the whole fact table. Additionally creating the copy lasts very
> long.
>
> It would be ideal if we had 24 monthly table partitions and could
> tell a table that it consists of only some partitions. That way we
> could create a new (monthly) partition and create an additional
> table which accesses the same partitions like the fact table
> (except the oldest month) and additionally the new month partition.
>
> Well, we tried this by using monthly tables and joining them with
> a union all view instead of partitions: It's ideal for the
> monthly processing, unfortunately the SQL queries of the
> customers last 3 times as long.
>
> Do you see what I want to achieve? Is there any other way for
> doing that?
>
> Regards,
> Alex

What you describe is a nightmare.
Why aren't you using Partitioning?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed Jun 29 2005 - 08:49:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US