Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: deleting large number of records from table...
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.atReceived on Wed Nov 12 1997 - 00:00:00 CST