Performance Tuning

From: James <jwilkie1_at_gmail.com>
Date: 20 Feb 2007 12:34:21 -0800
Message-ID: <1172003661.002173.286820@l53g2000cwa.googlegroups.com>

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. Received on Tue Feb 20 2007 - 14:34:21 CST