Question: I need to delete a large number of (2 million) tuples from a table of 5 million based on specified criteria. The criteria (condition) is fetched from one base table. I'll have to carry out this process on roughly 30 tables. Is there a quick way I can
You quite simply have too much data for it to be deleted quickly. No matter what method you use, it will take a significant amount of time. If you are confident of your code, you can also disable constraints while pruning your data, which will save some time.
The fastest way to delete from large tables
It might be easiest to create a new table with the rows you want to keep - then drop the old one but whatever you do will take a large amount of time
Another option you can try would be to create a new table using ctas and the select statement is based on the join condition between the two base tables. When done, index it as needed, then do a rename of the original followed by a rename of the second (this part takes < 1 second, but no one can have a lock on the table).
Tuning any DML statement can be difficult, but there are some things that
you can do to speed-up Oracle delete operations. For complete details,
see the book
Advanced Oracle SQL Tuning: The Definitive Reference.
Some helpful hints include:
- Use partitioning: The
fastest way to do a mass delete is to drop an Oracle partition.
- Tune the delete subquery: Many Oracle
deletes use a where clause subquery and optimizing the subquery
will improve the SQL delete speed.
- Use bulk deletes: Oracle PL/SQL has a
bulk delete operator that often is faster than a standard SQL
- Drop indexes & constraints: If you are
tuning a delete in a nighttime batch job, consider dropping the indexes
and rebuilding them after the delete job as completed.
- Small pctused: For tuning mass deletes
you can reduce freelist overhead by setting Oracle to only re-add a
block to the freelists when the block is dead empty by setting a
low value for
Lastly, resist the temptation to do
"soft" deletes, a brain-dead
approach that can be fatal.