Re: Performance Tuning

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Feb 2007 13:48:55 -0800
Message-ID: <1172008135.696917.105140@k78g2000cwa.googlegroups.com>

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.

Do you have a DBMS XPLAN? Maybe try to simplify:

The set up:
CREATE TABLE T1 (
  ID NUMBER(10),
  CODE VARCHAR2(4),
  QUANTITY NUMBER(10));

INSERT INTO T1 VALUES (1,'A',2);
INSERT INTO T1 VALUES (1,'A',3);
INSERT INTO T1 VALUES (1,'B',2);
INSERT INTO T1 VALUES (2,'A',1);
INSERT INTO T1 VALUES (2,'C',2);
INSERT INTO T1 VALUES (3,'A',4);

COMMIT; Starting point:
SELECT
  ID,
  CODE,
  SUM(QUANTITY) QTY
FROM
  T1
GROUP BY
  ID,
  CODE;         ID CODE QTY
---------- ---- ----------

         1 A             5
         1 B             2
         2 A             1
         2 C             2
         3 A             4

Next step:
SELECT
ID,

  MAX(DECODE(CODE,'A',QTY,NULL)) A_QUANTITY,
  MAX(DECODE(CODE,'B',QTY,NULL)) B_QUANTITY,
  MAX(DECODE(CODE,'C',QTY,NULL)) C_QUANTITY

FROM
  (SELECT
    ID,
    CODE,
    SUM(QUANTITY) QTY
  FROM
    T1
  GROUP BY
    ID,
    CODE)
GROUP BY
  ID;

ID A_QUANTITY B_QUANTITY C_QUANTITY ---------- ---------- ---------- ----------

         1          5          2
         2          1                     2
         3          4

DBMS XPLAN:

|   1 |  SORT GROUP BY NOSORT|      |      1 |      6 |      3 |
00:00:00.01 |       7 |       |       |          |
|   2 |   VIEW               |      |      1 |      6 |      5 |
00:00:00.01 |       7 |       |       |          |
|   3 |    SORT GROUP BY     |      |      1 |      6 |      5 |
00:00:00.01 |       7 |  9216 |  9216 |     1/0/0|
|   4 |     TABLE ACCESS FULL| T1   |      1 |      6 |      6 |
00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------

Try #2:
SELECT
ID,

  SUM(DECODE(CODE,'A',QUANTITY,NULL)) A_QUANTITY,
  SUM(DECODE(CODE,'B',QUANTITY,NULL)) B_QUANTITY,
  SUM(DECODE(CODE,'C',QUANTITY,NULL)) C_QUANTITY

FROM
T1
GROUP BY
ID;

ID A_QUANTITY B_QUANTITY C_QUANTITY ---------- ---------- ---------- ----------

         1          5          2
         2          1                     2
         3          4

DBMS XPLAN:

|   1 |  SORT GROUP BY     |      |      1 |      6 |      3 |
00:00:00.01 |       7 |  9216 |  9216 |     1/0/0|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      6 |      6 |
00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Your SQL statement?

What version of Oracle? What is the SORT_AREA_SIZE set to?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Feb 20 2007 - 15:48:55 CST