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

In article <647a9c$s51_at_camel12.mindspring.com>, "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?
>
>Thanks in advance for any help.
>Bill

First way:

- drop all indexes on the table
- delete the 1,500,000 rows
- recreate the indexes

Problem: the space of the 1,500,000 rows is not freed
         referential integrity (of the table is used as foreign key)

Second way:

- create new table
- insert the rows not to be deleted
- drop the indexes on the old table
- create the indexes on the new table
- drop the old table
- rename the new table

Problem: referential integrity (of the table is used as foreign key)

Third way:
- PL/SQL-program with loop (delete 1,000 records then commit)

I don't know which of these ways is the best to solve your problems. But you should have enough hints to solve it yourself.

Happl Received on Tue Nov 11 1997 - 00:00:00 CST