Optimizing a delete script

I have a script that needs to delete certain rows from 4 rather large tables. The 1st three are actually children of the 4th. I cannot use FK constraints and CASCADing deletes because of the nature of this database (data warehouse with too many R.I. problems to deal with). The rows I want to delete from the child tables are all related to the same set of rows that I want to delete from the parent. The child delete statement looks essentially like this...

delete from child1
  where col1 in (select col1 from parent where ref_date < '&&cutoff');

Is it better to repeat this process 3 times, seeing that the select from the parent table will be identical for all 3? It seems to me like that would require a full scan of the parent table 3 times and I'm only actually interested in < 5% of it's rows. Will ORACLE use the same cursor over again 3x and therefor not need to re-scan the table for the 2nd and 3rd passes? If not, which of the following would be better:

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. Received on Thu Feb 22 1996 - 00:00:00 CET