Re: What is wrong with this query?

From: Chuck Hamilton <chuckh_at_ix.netcom.com>
Date: 1995/05/16
Message-ID: <3paur5$d1q_at_ixnews3.ix.netcom.com>#1/1


In <Pine.HPP.3.91.950515175227.2002B-100000_at_stimpy.mfa.com> Dave Erickson <erickson_at_mfa.com> writes:
>
>On 15 May 1995, Chuck Hamilton wrote:
>
>> Date: 15 MAY 1995 21:30:01 GMT
>> From: Chuck Hamilton <chuckh_at_ix.netcom.com>
>> Newgroups: comp.databases.oracle
>> Subject: Re: What is wrong with this query?
>>
>>
>> 2. This select might be a bit faster...
>>
>> select bill_acct_id from transmission where not exists (select *
 from
>> convert_acct where acct_id = transmission.bill_acct_id);
>> --
>> ><> Chuck Hamilton <><
>> ><> chuckh_at_ix.netcom.com <><
>Chuck,
> Doesn't that make for a co-related sub-query? Ie, the inner
 select
>has to be re-done for every row fetched in the outer query?
> I would think the original way would be faster, provided he
 indexed
>the convert_acct table correctly, and maybe added a 'distinct' to the
 sub
>query.
>
> Just askin....
>

Every time I've ever timed it, EXISTS / NOT EXISTS outperformed IN / NOT IN. Not sure why, but it does.

Not long ago I tried to develop a rule of thumb for using one over the other. I tried indexed v. non-indexed columns, large subqueries v. small subqueries, etc. The rule ended up being to always use EXISTS.

I should state though that I'm very new to this stuff. If someone has a better rule that can be proven in actual performance timings, I'm all ears.

-- 
   ><>    Chuck Hamilton    <><
   ><> chuckh_at_ix.netcom.com <><
Received on Tue May 16 1995 - 00:00:00 CEST

Original text of this message