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;
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