Re: Large table monthly switch design problem

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 30 Jun 2005 09:04:47 +0200
Message-ID: <da05i7$i8v$05$1@news.t-online.com>

schonlinner_at_yahoo.com schrieb:
> Hi AK,
>
>

>>Can you stop exposing the table altogether, granting select privilges
>>to views only?

>
>
> Well, basically we can do so, but it does not help. If users ask
> how many bookings there have been from America to Tokyo in March 2004
> grouped by the airplane type used.
>
>

>>Can you just create another view for your quality check team?
>>That view should not include the oldest patition.

>
>
> Yes, we can do that, of course. But how do we do the production switch,
> i.e. putting exactly that "view" online? I know, it would be easy, but
> look:
>
> As mentioned before: I tested it using a view which then contained
> a union all over 24 tables containing the monthly data. But the
> performance was round about 3 times slower.
>
> The main problems are:
> 1. How can we give the quality check team a chance to test the data
> before going into production?
> 2. How can we put (almost) exactly that quality checked table into
> production?
>
> Best regards,
> Martin
>

Mainly your problem is just covered by other people, just some thoughts in addition.
What are exactly that checks being done by QA? Usually data integrity should be ensured in Oracle by means of constraints. Of course it is a performance impact if you load data into datawarehouse and in many warehouses constraints are disabled by load. But you have no choice if you are not sure, that your data is "clean" by means of business logic. No choice, because you get with Oracle constraints more work done with less resource usage than if you will do it manually. If their checks are based on some kind of aggregated thresholds - for this purposes Oracle has things called "Materialized Views" , they should be fresh and you can fully rely on calculations being done by them.  From all i have read in this thread, i have impression, your QA team has intention to substitute some built-in functionality of Oracle... that , usually goes wrong... And what is the reason to use view with union over 24 nonpartitioned tables , are you preparing downgrade from 10g to 7.3 ?

Best regards

Maxim Received on Thu Jun 30 2005 - 02:04:47 CDT