Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: