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.
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.continue reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.continue reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.