| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> REPOST: Re: Can joins be less efficient?
I had to think very hard, but found one possibility (which has been true in
the distant past)
select a.a, a.b, a.c, a.d, a.e, sum(b.x)
from a, b
where a.a = b.a
group by a.a, a.b, a.c, a.d, a.e
Could be slower than
select a.a, a.b, a.c, a.d, a.e
from a
followed by
select sum(b.x) from b
where b.a = <key value>
This is because of the cost of grouping (sorting and temp table etc). Also, you can fetch the related rows as required e.g. when scrolling down a list, even if you front-end only allows fetching of entire result sets.
It may be that the optimiser has improved (but I don't think so) to avoid this problem. You can, of course, rewrite the query to nest the summation.
NoSpam <NoSpam_at_NoSpam.com> wrote in message
news:a2pj0t$sdh$1_at_ih292.ea.unisys.com...
> I'm working on the code written by somebody else. What this code does is
> that it makes a query to the DB to obtain a resultset. Then from each row
of
> the resultset, it repeatedly makes a second query to obtain a second
> resultset by using the key field in the first as a key to the second. It
is
> very obvious that the two queries can be combined with a simple join. But
> this person insisted that there are times that cascaded reading of the
> resultsets can be faster than a simple join but he couldn't give me an
> example. I just wonder if it really can be true and under what
circumstances
> that reading two resultsets can be faster than a single resultset with a
> join when a common key field exists in both tables.
>
> TIA
>
>
>
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Thu Jan 24 2002 - 15:10:54 CST
 |
 |