Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DBA FIGHT

Re: DBA FIGHT

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Wed, 25 Jun 2003 03:42:22 GMT
Message-ID: <xS8Ka.11472$9J4.11106@news02.roc.ny.frontiernet.net>

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EF91162.BAEDB974_at_exxesolutions.com... --snip--
> 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)
>
>

Daniel,

Without doing any test:
As far as cost is concerned ... my guess would be "exists". Assuming plain dbms_stats statististics.

However, I would not consider a query to be faster just on the basis of "cost". True, it will get my interest ... however, I would be more interested in two more things: *) The block gets that each query requires. Lower figure will get my interest. *) A rough comparison of timings by either using plain set timing on .. or using get_time.

Now you kinda know your dataset, which is unknown to me, and which matters quite a lot in this discussion (cardinality .. skew_factor).

In the end, I think I would lean toward exists for table sizes you mentioned. For bigger table sizes I would also be inclined to tryout the hash_sj hint for exists.

In the end: as I said - I need to have an idea of the dataset I'm dealing with to make a more informed decision. servers.serv_id's cardinality and serv_instance.serv_id's cardinality.

:)
Thanks,
Anurag Received on Tue Jun 24 2003 - 22:42:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US