Re: Optimizing a delete script

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1996/02/26
Message-ID: <Pine.SUN.3.91.960226091815.2163C-100000_at_seatimes>#1/1

On Thu, 22 Feb 1996, Chuck Hamilton 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
 

> 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

Write a PL/SQL block that reads the parent table once. Issue the delete for the child tables and then delete current for the parent (if that's in the plans).

I found it best to drop all RI, all extranious indexes, to get the performance up.

The other route is as you indicated, build a table of those 5% and use that to remove from the other tables.

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-382-8898         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |
| Seattle, WA 98111    Packet:  KG7JE_at_N6EQZ.WA       |
+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Mon Feb 26 1996 - 00:00:00 CET