Question:  I have a table that needs about 1/12 of it deleted every month. I can buy partitioning if it's recommended to improve delete throughput and I'm not worried about using redo and having to roll-back the deletes.  What are some ways to improve delete performance speed?

Answer: A delete is like any other DML (data manipulation language) statement, and Oracle provides several techniques for doing large batch deletes at a faster speed:

• Implement partitioning - Removing large volumes of adjunct data is a related partition is faster.  If you can segregate the data to be deleted into a separate partition, super fast deletes become easy with the "alter tablespace xxx drop partition" syntax.  If you are not licensed for Oracle partitioning, and you load your rows in-order, you could roll-your-own partitioning, using different tables names for each partition.
   

• Parallelize deletes - Oracle parallel DML includes delete statements and you can parallelize large deletes for faster performance.  You can also submit multiple, simultaneous delete statements against the same table, just make sure that you have enough freelists (or ASSM) to handle the concurrency.
   

• Have a larger blocksize - The time delay in an Oracle delete is largely the time spent writing the new blocks to disk, and placing the table into a 32k blocksize will marginally speed-up delete DML because there will be more rows deleted before a physical write.  This speed difference can range from zero up to ten percent, depending on the type of delete and the sequencing of the rows.
   

• Drop indexes - Dropping indexes before a mass delete and rebuilding them afterwards can improve delete performance because each individual delete would have to remove itself from the index, causing slowdowns.  Oracle removes index entries without re-balancing the index tree (a "logical delete"), but this is still time-consuming, especially if you have lots of indexes on the target table.
   

• Use bulk binds - In certain cases using bulk collect/forall can improve delete performance.  Bulk Binds are a PL/SQL technique where, instead of multiple individual delete statements, all of the operations are carried out at once, in bulk. This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine.

Using bulking for delete performance

The bulk delete operation is the same regardless of server version.  Using the forall_test table, a single predicate is needed in the WHERE clause, but for this example both the ID and CODE columns are included as if they represented a concatenated key.

The delete_forall.sql script listed below is used for this test.  The script contains rollback statements, which are necessary to make sure the bulk operation has something to delete.  Since the script uses separate collections for each bind, it is suitable for all versions of Oracle that support bulk operations.

delete_forall.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
  TYPE t_code_tab IS TABLE OF forall_test.code%TYPE;

  l_id_tab    t_id_tab   := t_id_tab();
  l_code_tab  t_code_tab := t_code_tab();
  l_start     NUMBER;
  l_size      NUMBER     := 10000;
BEGIN
  -- Populate collections.
  FOR i IN 1 .. l_size LOOP
    l_id_tab.extend;
    l_code_tab.extend;

    l_id_tab(l_id_tab.last)     := i;
    l_code_tab(l_code_tab.last) := TO_CHAR(i);
  END LOOP;

  -- Time regular updates.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_id_tab.first .. l_id_tab.last LOOP
    DELETE FROM forall_test
    WHERE  id   = l_id_tab(i)
    AND    code = l_code_tab(i);
  END LOOP;

  ROLLBACK;

  DBMS_OUTPUT.put_line('Normal Deletes : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  l_start := DBMS_UTILITY.get_time;

  -- Time bulk updates.

  FORALL i IN l_id_tab.first .. l_id_tab.last
    DELETE FROM forall_test
    WHERE  id   = l_id_tab(i)
    AND    code = l_code_tab(i); 

  DBMS_OUTPUT.put_line('Bulk Deletes   : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  ROLLBACK;
END;
/

Before running the delete_forall.sql script make sure the forall_test table is populated using the insert_forall.sql script or there will be no records to delete.

SQL> @delete_forall.sql
Normal Deletes : 416
Bulk Deletes   : 204

PL/SQL procedure successfully completed.

The performance of the bulk delete is similar to the performance of the bulk update; the bulk operation is approximately twice the speed of the conventional operation.

 

---