Re: Optimizing a delete script

From: Mark Nielsen <mnielse_at_wilma.uswnvg.com>
Date: 1996/02/26
Message-ID: <4gt6sf$dd7_at_fred.uswnvg.com>#1/1


If col1 is indexed in your parent table, another solution is to use 'exists' in your delete query:

delete from child1
where exists (select 'x' from parent where parent.col1 = child1.col1 and ref_date < to_date('&&cutoff'));

Mark Nielsen
Consultant

Chuck Hamilton (chuckh_at_ix.netcom.com) wrote:
: 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 Mon Feb 26 1996 - 00:00:00 CET

Original text of this message