RE: Deletion Of 160 Million Rows.

I used the 2nd method recently where I had to remove 33M rows out of a 110M rows table. I first collected all rowid's of rows to be removed in a temp table with just
a rowid column. Then I started deleting rows in batches of 100000 using this temp table.
Since the 110M table was a insert-only table based on (audit)date there was no danger in compromising intetrity if I was going to spread out the delete batches over a week
to avoid too much extra UNDO/REDO (archivelog!) generation on a single day.

In fact, nothing new to Tom's suggestion

Regards,
Andre van Winssen

-----Oorspronkelijk bericht-----

Van: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]Namens Mercadante, Thomas F Verzonden: dinsdag 8 februari 2005 14:07 Aan: 'sheldonquinny_at_gmail.com'; oracle-l_at_freelists.org Onderwerp: RE: Deletion Of 160 Million Rows.

Sheldon,

Consider redesigning your table to use partitioning. You could then simply drop a partition of the old data that you want to get rid of, and add new partitions as new data needs to be loaded to the table.

To delete the records, there are two approaches:

1). Create a new table and insert the records you want to keep into it. Drop the old table and renamed the new table back to the old name. Tis is probably going to be faster than a delete process. You could even insert "groups" of rows to better manage the process.

2). Write a delete process that deletes records in groups with a commit in between. I would do this as follows:

Create a dummy table and populate it with the PK's of the records to be deleted. Write a PL/SQL loop that reads this table and deletes records from your target tables. After every 100,000 deletes, commit. You could make this restartable by having a column in the dummy table indicating that the delete took place. Once all the records are deleted, drop the dummy table.

I like the second option better than the first, but that's just me. They both work fine.

Good Luck.

Tom

-----Original Message-----

From: Sheldon Quinny [mailto:sheldonquinny_at_gmail.com] Sent: Tuesday, February 08, 2005 4:39 AM To: oracle-l_at_freelists.org
Subject: Deletion Of 160 Million Rows.

Hi,

I Would Just LIke To Ask Whether It Is Possible To Delete 60 Million Rows. At A Strech And Without Undo Contention. The Requirenment Is To Delete 60 Million Records From The 160 Million Records. Its An OLTP System.

Is There An Way To Lessen The Effort Taken By Server Process. SInce Its An OLTP DB.
Answers Should Be Related To Oracle 9x.

Sheldon.
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 08 2005 - 08:28:23 CST