Re: DBA FIGHT

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EF8BB1A.97D6D5EC_at_exxesolutions.com...
> Gene wrote:
>
> > In terms of performance a direct table join will produce the best
> > performance, a correlated sub query with an EXISTS clause will produce
> > better results than a query using the IN function.
>
> > <snipped>
>
> Your statement is often true but not always true.
>
> Take the following two queries for example with 100 records in the Server
> table and 1000 records in the Serv_Inst table:
>
> SELECT srvr_id
> FROM servers s
> WHERE EXISTS (
> SELECT srvr_id
> FROM 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);
>
> Want to bet which is faster? On 8.1.7.3, on 9.2.0.1?
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>

:)
Your comment "Your statement is often true but not always true" I fully agree with. However, I'm willing to take you on your bet. The example just does not contain enough info for you to make an assertion whether one query will be faster than the other.

I hope you are not betting for the "not in" query!

Anurag Received on Tue Jun 24 2003 - 19:51:14 CDT