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?
Received on Tue Feb 20 2007 - 15:46:56 CST
