Re: DBA FIGHT

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Tue, 24 Jun 2003 20:05:06 -0700
Message-ID: <3EF91162.BAEDB974@exxesolutions.com>

Anurag Varma wrote:

> "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

I have done this demo in front of my students at the U for two years now. In a week or two I will do it again for this summer's class.

On the same machine I run DBMS_STATS and then EXPLAIN PLAN six statements that produce the same result set from the data.
These are the two that have the lowest cost. The EXISTS is the lowest cost in 8.1.7 and the ridiculous NOT IN with a MINUS that I wrote trying
to kick the cost up is the lowest in 9.2.0.1.

So ... yes I am. Here is the full set of statements:

SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

SELECT srvr_id
FROM servers
WHERE srvr_id IN (

   SELECT srvr_id
   FROM serv_inst);

SELECT srvr_id
FROM servers
WHERE srvr_id IN (

   SELECT i.srvr_id
   FROM serv_inst i, servers s
   WHERE i.srvr_id = s.srvr_id);

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);

SELECT srvr_id
FROM servers s
WHERE EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

Want to guess on the one with the highest cost?

--
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)

Received on Tue Jun 24 2003 - 22:05:06 CDT