Large table monthly switch design problem

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 Received on Wed Jun 29 2005 - 08:13:20 CDT