Re: Optimizing a delete script

afischer_at_ea.com wrote:
>jared_at_valleynet.com (Jared Still) wrote:
>>chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:
>>
>>>delete from child1
>>> where col1 in (select col1 from parent where ref_date < '&&cutoff');
>>
>>One thing to do is structure your statement to use 'exists' rather
>>than 'in';
>>
>>delete from child1 c
>> where exists
>> (
>> select null
>> from parent p
>> where ref_date < '&&cutoff'
>> and p.col1 = c.col1
>> );
>
>A correlated "Exists" would be faster than "In"? Hmmmm..... seems doubtful to me...
>
>
Oh yee of little faith. A correlated exists WILL be faster than an IN clause depending on the size of the tables. If the IN clause returns few rows or the child1 table is large then the IN clause will be quicker but if the sizes are similar or child1 is small then the EXISTS wins every time.

• Unless of course the reference table PARENT is a synonym across a database link and then say goodbye to performance anyway.