Re: Optimizing a delete script

In article <4h7jie$gg8_at_viper.ea.com>, afischer_at_ea.com writes:
|>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...

EXISTS can be faster than IN. That dependes on the case you are having. I would try both and compare which is faster. Make sure that you have indexes in col1 and ref_date columns.

• Harri

  ---

  Harri Kaukovuo, Oracle Corporation hkaukovu_at_us.oracle.com ** The statements and opinions expressed here are my own and ** do not necessarily represent those of Oracle Corporation.