Re: DBA FIGHT

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)