Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> group by is very slow .. is there different way

group by is very slow .. is there different way

From: <nova1427_at_gmail.com>
Date: Sun, 9 Dec 2007 04:11:39 -0800 (PST)
Message-ID: <136e19cc-b793-4ca3-a4c8-bfd3c1e52e98@b40g2000prf.googlegroups.com>


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
Received on Sun Dec 09 2007 - 06:11:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US