Re: Performance Tuning

On Feb 20, 3:46 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com> wrote:
> On Feb 20, 3:34 pm, "James" <jwilk..._at_gmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I work on a data warehouse and hit the brick wall on this one. Here
> > is the problem:
>
> > The source data we are trying to load looks like this (Table1)
> > ID Code Quantity
> > 1 'A' 2
> > 1 'A' 3
> > 1 'B' 2
> > 2 'A' 1
> > 2 'C' 2
> > 3 'A' 4
> > ...
>
> > Our user wants to see one row per ID, like this (Table2)
> > ID A_Quantity B_Quantity C_Quanty
> > 1 5 2 null
> > 2 1 null null
> > 3 4 null null
>
> > There are a few challenges we face:
> > 1. We have three different codes now but new ones can be added anytime
> > without us knowing. So we don't want to design a table 2 because it'll
> > become a nightmare to maintain.
> > 2. The amount of data is very large (40 million rows).
> > 3. We have limited system resources to work with.
>
> > So I loaded the data just like table1 and created a view look like
> > this:
>
> > create view myview as
> > select a.a_q, a.b_q, a.c_q, b.id, b.other_things
> > from
> > (Select max(a_quantity) a_q, max(b_quantity) b_q, max(c_quantity)
> > c_q
> > from
> > (Select
> > sum(decode (quantity, code, 'A' ,quantity, null)) as
> > A_quantity,
> > sum(decode (quantity, code, 'B', quantity, null)) as
> > B_quantity,
> > sum(decode (quantity, code, 'C', quantity, null)) as
> > C_quantity
> > from table1
> > group by ID, Code )
> > group by ID) a, some_other_large_table b
> > where a.id = b.id)
>
> > The problem we are running into is when querying this view, a very
> > very large amount of temp space is used. Beause there are other
> > schemas using the same temp space, we are blowing everybody else out
> > of the water. What other options do I have?
>
> > Thanks!
>
> > James.
>
> I am not very clear on the business logic behind this so I will just
> consider that all is the way it should be. Have you considered using a
> materialized view instead of the view?- Hide quoted text -
>
> - Show quoted text -

Thanks for your reply. We had considered materialized view. But the problem is the amout of our data is very large, about 20M rows per week. Refreshing the materialized view will become very very painful very quickly. Our last resort is to create multiple materialized views, one for each week's data. But then we have the same problem when a new code shows up.

James. Received on Tue Feb 20 2007 - 16:01:33 CST