Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: deleting large number of records from table...

Re: deleting large number of records from table...

From: Heber Ferraz-Leite <hferraz_at_via.at>
Date: 1997/11/12
Message-ID: <34695365.2055786@news.via.at>#1/1

On Mon, 10 Nov 1997 09:46:46 -0600, "bill" <beers_at_mindspring.com> wrote:

>Could anyone give advice for deleting a large number of records from a
>table. I am trying to remove about 1,500,000 records and I am running out
>of rollback space. Would it be better to create a duplicate table structure
>and copy the few records to it, then drop the first table?
>

Making a commit every x records is a good idea. Nevertheless, I have found out that when the transaction takes very long, and this should be the case with 1,500,000 records, you run into the "rollback segment too old" problem.

The approach I have taken is to create a temporary table containing the rowid of every record to be deleted. (create the temporary table and then: insert into temptable select rowid from yourtable where ... and the where clause you would use in the delete).

Then define a cursor to the temporary table and for every record do a delete from yourtable where rowid is the current rowid stored in the temptable.

Every 300, or 1000 records or so do a commit.

I hardly influences the performance of the database and needs basically no rollback segments, except for the creation of the temporary table (which we do during the night hours).

Heber

---
hferraz_at_via.at
Received on Wed Nov 12 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US