Re: group by is very slow .. is there different way

On Dec 9, 5:22 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 9, 7:11 am, nova1..._at_gmail.com wrote:
>
>
>
>
>
> > I try to write select SQL for table1 & table2 to make it like
> > table_result
>
> > The idea takes the rows dependent on ID and TYPE and maximum amount to
> > take VALUE
>
> > table2
> > FK TYPE VALUE AMOUNT
> > 1111 A 3 18
> > 1111 A 2 21
> > 1111 B 3 32
> > 2222 B 2 43
> > 2222 B 4 53
> > 2222 C 2 23
> > 2222 G 1 45
> > 3333 D 6 22
> > 3333 A 4 66
> > 3333 A 3 54
> > 3333 A 6 76
> > 3333 A 2 44
> > 3333 G 3 99
>
> > table1
> > PK ID NAME
> > 1111 110011 aaaaaa
> > 2222 220022 bbbbbb
> > 3333 330033 cccccc
>
> > table_result
> > ID TYPE VALUE AMOUNT
> > 110011 A 2 21
> > 110011 B 3 32
> > 220022 B 4 53
> > 220022 C 2 23
> > 220022 G 1 45
> > 330033 D 6 22
> > 330033 A 6 76
> > 330033 G 3 99
>
> > This is my query put the problem of this query is very slow. Is there
> > any other idea?
>
> > SELECT
> > table1.ID,
> > table1.NAME,
> > MAX(CASE WHEN table2.TYPE='A' THEN table2.AMOUNT END) A,
> > MAX(CASE WHEN table2.TYPE='B' THEN table2.AMOUNT END) B,
> > MAX(CASE WHEN table2.TYPE='C' THEN table2.AMOUNT END) C,
> > MAX(CASE WHEN table2.TYPE='D' THEN table2.AMOUNT END) D,
> > MAX(CASE WHEN table2.TYPE='G' THEN table2.AMOUNT END) G
> > FROM table1 left outer join table2 on (table1.PK=table2.FK
> > AND (table2.FK, table2.AMOUNT, table2.TYPE) IN (
> > SELECT
> > table2.FK,
> > MAX(table2.AMOUNT),
> > table2.TYPE
> > FROM table1, table2
> > WHERE table1.PK=table2.FK
> > GROUP BY
> > table2.FK,
> > table2.TYPE
> > )
> > )
> > GROUP BY
> > table1.ID,
> > table1.NAME
>
> It is somewhat hard to determine where the performance problem is
> originating. It could be a bug related to the ANSI join, but is most
> likely related to the subquery "(table2.FK, table2.AMOUNT,
> table2.TYPE) IN (" potentially causing full tablescans for each row in
> the join. A DBMS_XPLAN would be helpful. This can be done on Oracle
> 10g by adding a hint immediately after the word SELECT:
> SELECT /*+ GATHER_PLAN_STATISTICS */
>
> Once the query completes, display the results of the DBMS_XPLAN:
> SELECT
> *
> FROM
> TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
>
> You might be able to use the analytical version of MAX() to determine
> the maximum amount when TABLE1 and TABLE2 are left outer joined to
> each other. You would then slide this result into an inline view and
> filter out any rows where the analytical version of the MAX() was not
> equal to the value of amount. For example, assume that I had a table
> T1 with the following rows:
> C1 C2
> -- --
> A 1
> A 2
> A 3
> A 4
> A 6
> B 8
>
> SELECT
> C1,
> C2,
> MAX(C2) OVER (PARTITION BY C1) C2_MAX
> FROM
> T1;
>
> C1 C2 C2_MAX
> -- -- ------
> A 1 6
> A 2 6
> A 3 6
> A 4 6
> A 6 6
> B 8 8
>
> Now, filter out the rows that are not the same as C2_MAX:
> SELECT
> C1,
> C2
> FROM
> (SELECT
> C1,
> C2,
> MAX(C2) OVER (PARTITION BY C1) C2_MAX
> FROM
> T1)
> WHERE
> C2=C2_MAX;
>
> C1 C2
> -- --
> A 6
> B 8
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thank you so much
it is very fast

thank you for your help Received on Sun Dec 09 2007 - 10:27:56 CST