Re: What is wrong with this query?
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