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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top