Oracle delete Tuning

Oracle Database Tips by Donald BurlesonMarch 16, 2015

Question: How can you tune an Oracle delete SQL statement? I need to delete lots of rows?

Answer: It's difficult to tune any DML statement 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.

Also see: The fastest way to delete from large tables and see deleting large numbers of rows quickly.

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 delete.
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 pctused.
Parallelize the delete job: You can run massive delete in parallel with the parallel hint. If you have 36 processors, the full-scan can run 35 times faster (cpu_count-1)
Consider NOARCHIVELOG: Take a full backup first and bounce the database into NOLOGGING mode for the delete and bounce it again after, into ARCHIVELOG mode.
Use CTAS: Another option you can try would be to create a new table using CTAS where the select statement filters out the rows that you want to delete. Then do a rename of the original followed by a rename of the new table and transfer constraints and indexes.
Break the delete into pieces: See below for a techniques to keep you from blowing-out your undo logs during large delete operations.

Lastly, resist the temptation to do "soft" deletes, a brain-dead approach that can be fatal.

Deleting billions of rows

(by Rampant author Laurent Schneider)

To delete large number of rows, for instance rows with date until 2015, you can issue this simple statement.


SQL> DELETE FROM T WHERE C<DATE '2011-01-01';
1'000'000'000 rows deleted 
Elapsed: 23:45:22.01
SQL> commit;

This is perfectly fine. The table remains online, other users are not much affected (maybe they will not even notice the lower IO performance).

It will generate quite a lot of UNDO, and you will need enough space for archivelog and a large undo tablespace and a large undo retention setting (to prevent ORA-01555 snapshot too old).

If your table is like 100G big, you do it during week-end, you have 500Gb Undo and 250G free space in your archive destination, you will be fine. Well. Maybe.

There are workarounds where you create a new table then rename etc… but this is not the scope of this post and you will need to validate your index / foreign keys / online strategy with the application guys.

Another way to decrease runtime pro statement and undo requirement pro statement (but increase overall elapsed time) is to divided it chunks, for instance to delete 100'000'000 rows each night during 10 days.


SQL> DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM<=100000000;
100'000'000 rows deleted 
Elapsed: 04:11:15.31
SQL> commit;

Or if you want to delete in much smaller chunks to accommodate your tiny undo tablespace, you could try this


BEGIN
  LOOP
    DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM <= 1000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
   END LOOP;
END;
/

This will run longer than a single transaction, but it is quite useful if your undo tablespace is too small. Also if you abort it (CTRL-C or kill session), you will not lose all progresses (but you lose on integrity/atomicity) and your KILL SESSION will not last for ever. With a single transaction, your session may be marked as killed for hours/days.

��