Re: performance strategies for many joins

Brian E Dick wrote:

> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E3AAADB.B5179B79_at_exesolutions.com...
> >
> > SELECT DISTINCT s.srvr_id
> > FROM servers s, serv_inst i
> > WHERE s.srvr_id = i.srvr_id;
> >
> > SELECT DISTINCT srvr_id
> > FROM servers
> > WHERE srvr_id NOT IN (
> > SELECT srvr_id
> > FROM servers
> > MINUS
> > SELECT srvr_id
> > FROM serv_inst);
>
> I'll bite. Since you're looking for non-intuitive answers, I would say the
> second query would tend to do full table scans. Multiblock reads may make it
> faster, though.
>
> Do I win a prize (booby or otherwise)?
>
> Later,
> BEDick

In tests run with both 8i and 9i a straight inner join had a cost at least 2X greater than the second SQL statement.

Given my intro I would expect everyone to make the correct choice. But shown to them blind ... I would expect everyone to write the simple inner join. (well almost everyone).

Daniel Morgan Received on Fri Jan 31 2003 - 17:26:45 CST