Re: delete and rollback segs

From: Mikko Lahti <lahti_at_pouta.fmi.fi>
Date: 27 Apr 92 17:31:00
Message-ID: <LAHTI.92Apr27173100_at_pouta.fmi.fi>

In article <1992Apr26.213118.22025_at_cnw01.storesys.coles.oz.au> nigel_at_cnw01.storesys.coles.oz.au (Nigel Harwood) writes:

> We are developing an application which does some large deletes on
> tables and are finding that our roll back segments are overflowing.

    [ unsucsessful tries deleted ]

> Anyone have any other ways that they have gotten around this?
 

> <<<<<<<<<<<<<<<<<<<<<<<<< Nigel Harwood >>>>>>>>>>>>>>>>>>>>>>>>>>>
> << Post: Coles Supermarkets, PO Box 480 Glen Iris 3146, Australia >>
> << Phone: +61 3 829 6090 E-mail: nigel_at_cnw01.storesys.coles.oz.au >>
> << FAX: +61 3 829 6886 >>

I have used two ways:

1. If you are deleting most lines of a table, then instead of deleting
   • create an help_table that contains the data that will not be deleted using CREATE TABLE new_table AS SELECT * FROM old_table WHERE ... and the condition for keeping the record
   • drop the old table
   • RENAME new_table TO new_table This won't only save rollback segments, it is also faster. But it don't work if the table must be able to be used all the time.
2. Cut the delete into smaller deletes and COMMIT between them. COMMIT will clear the rollback segment. But if your delete must be in one transaction (all or nothing to be deleted) then you have problems.

If you find some other ways to work this out, please mail. I have these problems every now and then.

Try to keep on

Mikko Lahti                       Mikko.Lahti_at_fmi.fi

Finnish Meteorological Institute, Helsinki, Finland Received on Mon Apr 27 1992 - 17:31:00 CEST