HELP! --- Best way to delete large amounts of data.

I have been given this problem, and the DBA is out on vacation. Hopefully someone has had to deal with this before and can help.

I have a large database (25 million records) on a AIX box. I need to archive and then delete about 5 million of the records. There are plenty of child records that also need to be archived and deleted

The archive program I inherited takes about 3 days to run -- way too long. However, it is logically correct. It opens a cursor on the main table for all records to archive / delete. For each record to be archived / deleted, it finds all the children, then archives / deletes them. Finally, after a given number of records on the main table have been processed, it issues a commit. This process is restartable, and, frankly, is what I would have written if I had to start from scratch. But the 3 days to run is unacceptible.

Anyone have any approach that they recommend? Also would love to hear about approaches that do not work!

Thanks

Nick. Received on Tue Dec 19 2000 - 16:53:24 CST