SQL EXPERTS... performance tune this query???

[lloydsj]

Hi, I'm trying to write the fastest sql i can and need some idea's. Hopefully someone can help. Data/tables is as below and can't be changed.

The tables in question both have MILLIONS of rows... and the number of attributes i'm pulling out will be more like 20 (rather than the 3 i've mocked up here). Also the tables (table1 and table2 ) will be getting writen to continuously, if that makes any difference.

table1

id	desc	detail1		detail2		detail3
######################################################
1	banana	jsdja		nbsdnbs		kjuif
2	apple	fruit		round		tree
3	orange	etc		etc		and so on


table2

id	table1_id	attribute	attribute_value
#################################################
1	1		blah		blah
2	2		colour		red
3	2		width		11
4	2		length		11
5	3		etc		etc
6	4		etc		and so on

What I'm after getting is as below...

desc	detail1	detail2	detail3	colour	width	length
######################################################
apple	fruit	round	tree	red	11	11

I've got to it with the sql below... but is there a better (i.e. faster!) way to do it???

select t1.*, t2_1.attribute_value colour, t2_2.attribute_value width, t2_3.attribute_value length
from table1 t1,
	 table2 t2_1,
	 table2 t2_2,
	 table2 t2_3
where t1.desc = 'apple'
and t1.id = t2_1.table1_id
and t2_1.attribute = 'colour'
and t1.id = t2_2.table1_id
and t2_2.attribute = 'width'
and t1.id = t2_3.table1_id
and t2_3.attribute = 'length';

hope that all makes sense.

Cheers.

 

[lloydsj]

Hi,

Yes i think SimonCHere is on the same wave length as what i want. I'm just checking out PIVOT and the last example using LEFT OUTER joins. cheers for the idea's so far.

Any suggestion as to which will perform the best? (between mine and SimonCHere's 2 ideas).

BTW. I'm using oracle 11g (which also has PIVOT)

 

[lloydsj]

Cheers SimonCHere, that last example is working spot on. Thanks

Cheers everyone else for your efforts too. Obviously, if anyone can think of something even better than im still interested (although i think SimonCHere has pretty much sorted it)