@Pedja: Ono rjesenje sa temp tabelom ce ti biti dosta sporije, nego da radis sa subselectima... Izbjegavaj to kad god mozes...
Inace, pokusao sam i ja da sklepam odgovarajuci upit, ali sam dosao do slicnog rjesenja kao zextra:
Kôd:
select t1.id, t2.id, t1.value, t2.value, t1.description, t2.description
from test_dejan t1, test_dejan t2,
(select min(value) min_value, max(value) max_value from test_dejan group by id) sub
where t1.id = t2.id
and t1.value = sub.min_value
and t2.value = sub.max_value
Ne znam da li mogu da kopiram ovdje output od explain plan komande, a da se tekst ispravno prikaze... ? Ja cu kopirati, a nek neko od moderatora doda odgovarajuce tagove za formatiranje teksta..
Prvo je izlistan rezultat moga upita, a potom rezultat upita, kojeg je postavio zextra.
Kôd:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 11.0246977930899
HASH JOIN 1 114 11.0246977930899
HASH JOIN 1 70 7.51653332072425
TABLE ACCESS FULL ONOPER.TEST_DEJAN 7 308 3.00797875329891
VIEW 7 182 4.00817220464087
SORT GROUP BY 7 182 4.00817220464087
TABLE ACCESS FULL ONOPER.TEST_DEJAN 7 182 3.00797875329891
TABLE ACCESS FULL ONOPER.TEST_DEJAN 7 308 3.00797875329891
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 7 15.533449004233
HASH JOIN 7 798 15.533449004233
HASH JOIN 7 581 12.0250878881496
HASH JOIN 7 399 7.51653332072425
TABLE ACCESS FULL ONOPER.TEST_DEJAN 7 217 3.00797875329891
VIEW 7 182 4.00817220464087
SORT GROUP BY 7 182 4.00817220464087
TABLE ACCESS FULL ONOPER.TEST_DEJAN 7 182 3.00797875329891
VIEW 7 182 4.00817220464087
SORT GROUP BY 7 182 4.00817220464087
TABLE ACCESS FULL ONOPER.TEST_DEJAN 7 182 3.00797875329891
TABLE ACCESS FULL ONOPER.TEST_DEJAN 7 217 3.00797875329891