On Feb 20, 3:48 pm, "Charles Hooper" <hooperc2..._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.
>
> 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:
> ------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> Time | Buffers | OMem | 1Mem | O/1/M |
> ------------------------------------------------------------------------------------------------------------------
> | 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:
> ----------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> Time | Buffers | OMem | 1Mem | O/1/M |
> ----------------------------------------------------------------------------------------------------------------
> | 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.- Hide quoted text -
>
> - Show quoted text -
We have 9i. sort_area_size = 4194304
The amount of data we have is huge. We might have hit a hard limit
because the amount of data it has to process.
Received on Tue Feb 20 2007 - 16:05:59 CST
