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...
>
>

I think I've pretty much decided on the PL/SQL route anyway. Then I have complete control over what happens when. Plus it seems to be faster.

for rec in parent_cursor loop
  delete from child1 where childcol = rec.col;   delete from child2 where childcol = rec.col;   etc...
  commit;
end loop;

--
Chuck Hamilton
chuckh_at_ix.netcom.com

If at first you don't succeed, skydiving isn't for you.