Q

How to delete tens of millions of rows from table

Are there any "best practices" for deleting tens of millions of rows from a table? My understanding is that this could generate a rather large undo file.

Are there any "best practices" for deleting tens of millions of rows from a table? My understanding is that this could generate a rather large undo file.
I prefer to perform one large DELETE statement so long as my Undo tablespace is big enough. If my Undo tablespace will not handle this much undo information, then I break up the DELETE statement into smaller units. For instance, assume I originally start with the following:
DELETE FROM my_table WHERE tab_id < 10000;
Instead, I might perform the following:
DELETE FROM my_table WHERE tab_id < 1000;
COMMIT;
DELETE FROM my_table WHERE tab_id < 2000;
COMMIT;
....
DELETE FROM my_table WHERE tab_id < 10000;
COMMIT;

Reader feedback:

Sanjay writes:

Just another thought: If a large percentage of rows are getting deleted, I might consider creating another temp table with nondeleted rows and then truncate/drop table and then rename temp table to original table. E.g., if I have 10 million rows and out of 10 million rows, I would only keep 200K rows, I would insert 200K rows into some temp table and then drop/truncate original table and then rename temp table to original table.

Brian Peasland responds:

This is a perfectly acceptable solution to the problem. However, keep in mind that with the TRUNCATE command, there is no rollback of this operation.

This was first published in May 2007

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close