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

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. Received on Sun Dec 09 2007 - 08:22:21 CST