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.
Dig Deeper on Oracle database backup and recovery
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.