RE: Deletion Of 160 Million Rows.
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'sheldonquinny@xxxxxxxxx'" <sheldonquinny@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 8 Feb 2005 08:07:04 -0500
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@xxxxxxxxx]
Sent: Tuesday, February 08, 2005 4:39 AM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
