Re: Optimizing a delete script

Chuck Hamilton wrote:
>
> I have a script that needs to delete certain rows from 4 rather large
> tables. TThe child
> delete statement looks essentially like this...
>
> delete from child1
> where col1 in (select col1 from parent where ref_date < '&&cutoff');
> 1. Create an index on ref_date (approx 1300 distinct values out of 1
> million total rows).
> 2. Create a temporary table that just contains col1 from the desired
> parent rows and use that in the "in" clause?
> 3. Something entirely different.
> --
> Chuck Hamilton
> chuckh_at_ix.netcom.com
>
> If at first you don't succeed, skydiving isn't for you.

Chuck,

This statement will be transformed into a join by the optimizer. So the best approach is to create an index on ref_date, as you already suspected. You may want to use the rule hint, as there is still a chance the cost-based optimizer will be fooled by the parent table and do a full table scan anyway. If there are only 1300 distinct values and both tables have proper statistics, probably the cost-based optimizer will take the correct approach.
If it is possible you should try to execute the three statements in sequence. The second and third may be faster to execute because some of the data is already in cache.

Hope this helps,

Sybrand Bakker
Senior IS Analyst
Bentley Systems Europe
Hoofddorp
The Netherlands Received on Mon Feb 26 1996 - 00:00:00 CET