On Feb 20, 5:05 pm, "James" <jwilk..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
If you have such hard resource constraints, it may be the time to look
into redesigning the table or creating an additional one with the
proper structure for what you need to do with it.
Received on Tue Feb 20 2007 - 16:20:44 CST
