The February issue of SQL Server Magazine (yes – much like Darth Vader I dabble on the dark side) had a very interesting article by Vic Newell titled “4 Ways to purge Non-Partitioned Data”. The basic premise is that in an ideal world all tables would be partitioned such that mass deletions could be accomplished via truncate or drop partition commands. But since we don’t live in an ideal world, what alternatives exist to delete millions or billions of rows from a table quickly and without excessive internalized processing (e.g. redo logs, undo, flashback, etc.). He refers to this as “batched deletes” and gives four examples that I’ll convert to equivalent Oracle syntax (where possible) – plus offer a few of my own specific to Oracle.

NOTE: I am not endorsing or promoting any method as being preferred or superior. Your results will of course vary depending on your situation, and I cannot say because X occurred for me that it will always occur. That would constitute committing the classical logic mistake known as “Modus ponens”, which means to assume that if A implies B, when A therefore B.

 

Baseline (a.k.a. Brute Force) Method

First I’ll establish the baseline scenario for comparisons. So I created and loaded a table as follows:

 

DROP TABLE BIG_TABLE PURGE;

CREATE TABLE BIG_TABLE ( COL1 INTEGER,

                       COL2 VARCHAR2(100),

                       COL3 CHAR(100),

                       COL4 DATE );                                  

BEGIN

  FOR I IN 1 .. 5 LOOP

    FOR J IN 1 .. 1000 LOOP

      INSERT INTO BIG_TABLE

        VALUES ( I, 'THIS IS A TEST', 'THIS IS A TEST', SYSDATE );

    END LOOP;

  END LOOP;

  COMMIT;

  FOR I IN 1 .. 10 LOOP

    INSERT INTO BIG_TABLE SELECT * FROM BIG_TABLE;

    COMMIT;

  END LOOP;

END;

/

CREATE INDEX BIG_TABLE_IDX ON BIG_TABLE ( COL1 );

COLUMN COUNT(*) FORMAT 999,999,999;

SELECT COUNT(*) FROM BIG_TABLE;

 

This table has 5.1 million rows where the first column’s (i.e. COL1’s) values range between one and five (controlled by the “I” loop variable). Therefore when we request the database delete all rows with a first column value equal to three that represents exactly 20% of the entire table or roughly one million rows. Here's the simple delete command code for that:

 

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-0';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  DELETE FROM BIG_TABLE WHERE COL1 = 3; ----<<<< SIMPLE DELETE

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #1 – Simple Batched Delete

The first method proposed by the original article is to repetitively delete fixed size batches of records that meet the filter criteria so as to keep the internal delete command’s operation reasonable (i.e. not stress UNDO space). Of course we’ve now added a loop, multiple commits, repetitive deletes, and the additional SQL overhead of an additional filter (i.e. ROWNUM <= BTACH_SIZE).

VARIABLE RUN_TIME NUMBER;

DECLARE

  T1 DATE;

  T2 DATE;

  BATCH_SIZE NUMBER := 10000;

BEGIN

  SELECT SYSDATE INTO T1 FROM DUAL;

  LOOP

    DELETE FROM BIG_TABLE WHERE COL1 = 3    ----<<<< BATCH DELETE

AND ROWNUM <= BATCH_SIZE;

    EXIT WHEN (SQL%ROWCOUNT=0);

    COMMIT;

  END LOOP;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO :RUN_TIME FROM DUAL;

END;

/

Method #2 – Sliding Window Delete

The second method proposed by the original article simply seeks to improve upon the first method by scanning the big table just once recording all the addresses of the rows to delete and then performing essentially the same repetitive batched deletes using a sliding window of those addresses. Personally I feel that this method is fundamentally flawed since it relies on ROW ID’s recorded into a table and then used for the iterative deletes. The problem is that the rows’ addresses could change in between and/or during processing yielding “record not found” errors. Hence the correct method would be to place an exclusive lock on the original big table such that the record addresses remain static. But many people abhor using the LOCK TABLE command, so I left it out.

 

DROP TABLE ROWS_TO_DELETE PURGE;

CREATE TABLE ROWS_TO_DELETE ( XXX ROWID );

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-2';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

  BATCH_SIZE NUMBER := 10000;

  LOBOUND    NUMBER := 1;

  HIBOUND    NUMBER := BATCH_SIZE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  INSERT INTO ROWS_TO_DELETE

    SELECT ROWID FROM BIG_TABLE WHERE COL1 = 3;

  COMMIT;

  LOOP

    DELETE FROM BIG_TABLE WHERE ROWID IN ( ----<<<< WINDOWED DELETE

      SELECT  XXX

      FROM    (

        SELECT  XXX, ROWNUM AS RN

        FROM    ROWS_TO_DELETE

        )

      WHERE   RN BETWEEN LOBOUND and HIBOUND

    );

    EXIT WHEN (SQL%ROWCOUNT=0);

    COMMIT;

    LOBOUND := HIBOUND + 1;

    HIBOUND := HIBOUND + BATCH_SIZE;

  END LOOP;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #3 – Minimal Impact DELETE

The third method proposed by the original article simply seeks to improve upon the second method by only doing a record at a time so as to lessen the impact of the operation on the system. That way this simple operation does not skew normal processing times by attempting something too large all at once. Of course that should mean the longest total run time with the least spike in demand for completing the delete operation, the principle idea being to get the job done without impacting anything and/or anyone else. However note once again the use of ROW ID. And now that the run time will be longer the problem of changing row addresses is only worsened (i.e. more likely). So once again not one of my favorites.

 

DROP TABLE ROWS_TO_DELETE PURGE;

CREATE TABLE ROWS_TO_DELETE ( XXX ROWID );

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-3';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  INSERT INTO ROWS_TO_DELETE

    SELECT ROWID FROM BIG_TABLE WHERE COL1 = 3;

  COMMIT;

  FOR C1 IN (SELECT XXX FROM ROWS_TO_DELETE) LOOP

    DELETE FROM BIG_TABLE WHERE ROWID = C1.XXX; ----<<<< MINIMIZED DELETE

    COMMIT; 

  END LOOP;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #4 – Bulked DELETE

The fourth method proposed by the original article simply seeks to leverage database specific features to streamline the internal processing – which in the case of the original article using SQL Server applied the INNER JOIN on a DELETE command. Since that’s not available for Oracle, I instead used the FORALL bulk capability. Note that the record list table variable has to fit in the database server’s memory (since PL/SQL executes on the server) so it has some obvious limitations. I guess I could have applied the prior batched logic so as to keep the record list variable to a known and reasonable size. However with cheap memory these days it just did not seem worthwhile. Your situation will of course vary. Finally note that this method also suffers from the ROW ID dilemma – but since it’s as close to an atomic operation as we’re likely to get it’s possible that the LOCK TABLE could be skipped in this solution. You decide.

 

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-4';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

  TYPE DELLIST IS TABLE OF ROWID;

  RECLIST DELLIST;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  SELECT ROWID BULK COLLECT INTO RECLIST

    FROM BIG_TABLE WHERE COL1 = 3;

  FORALL I IN RECLIST.FIRST .. RECLIST.LAST

    DELETE FROM BIG_TABLE WHERE ROWID = RECLIST(I); ----<<<< BULKED DELETE

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #5 – Parallel DELETE

The fifth method is my first additional technique not mentioned per se in the original article – using the parallel DML capabilities of Oracle via an optimizer hint. Of course many people prefer not to hard code optimizer hints within application code so maybe altering the table would be preferable. So once again you decide. However note that choosing the parallel degree is not just specifying your number of CPU’s. You’ll need to consider concurrent workloads, disk subsystem IO throughput, how many slave processes will be created (i.e. GROUP BY and ORDER BY increase the count), and whether an index or full table scan will result in better results. All that said this method will probably be the best universal choice for many people.

 

ALTER SESSION ENABLE PARALLEL DML;

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-5';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  DELETE /*+ FULL(t) PARALLEL(t,8) */ FROM BIG_TABLE t ----<<<< PARALLEL DELETE

    WHERE COL1 = 3;

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #6 – DBMS_PARALLEL_EXECUTE

The sixth and final method is my second and last additional technique not mentioned per se in the original article – using the Oracle supplied package DBMS_PARALLEL_EXECUTE. While this technique may seem overly complex, under the right circumstances the results could well warrant learning and using this new PL/SQL package. Of course my example below should have EXCEPTION handling logic – but I wanted to keep the example simple. I also suspect that the ALTER SESSION to enable parallel DML may not be strictly necessary.

 

ALTER SESSION ENABLE PARALLEL DML;

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-6';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

  l_sql_stmt VARCHAR2(1000);

  l_try NUMBER;

  l_status NUMBER;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

 

  -- Create the TASK

  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');

 

  -- Chunk the table by SELECT using ROWID

  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'BERT', 'BIG_TABLE', true, 10000);

 

  -- Execute the DML in parallel

  l_sql_stmt := 'DELETE /*+ ROWID (dda) */ FROM BIG_TABLE

      WHERE col1 = 3 and rowid BETWEEN :start_id AND :end_id';

  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,

                                 parallel_level => 8);

 

  -- Done with processing; drop the task

  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');

 

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

 

The Results

OK, we now have a total of seven ways to write a very simple delete command. So which solution is the best (at least on my tests systems and for my test scenario)? Remember don’t extrapolate my results to suggest any universal conclusions. You’ll need to experiment to be sure on a case by case basis. Here are my results using two different test systems.

 

 

Run Time in SECONDS

 

Dual Core CPU, 8GB RAM,

Solid State Disk (SSD)

Quad Core CPU, 16GB RAM,

8 Disk RAID-0 with 512 MB Cache

Original Simple DELETE

82

30

Simple Batched DELETE

100

62

Sliding Window DELETE

137

87

Minimal Impact DELETE

267

193

“FORALL” Bulk DELETE

119

74

Parallel DML DELETE

61

21

DBMS_PARALLEL_EXECUTE

159

99

 

So for my server hardware and test scenarios it looks like the simple delete command with and without the optimizer hint are my best choices. Would you have guessed that? Of course in my test case I had no other concurrent applications or code running, so I did not have to contend with sharing UNDO, REDO, TEMP space and other resources. I’m sure that if I had then the results could well have been different – with a different set of winners. So test these approaches to be sure, because often what sounds good in theory may not always be good in reality. You generally need empirical results from a similar situation in order to choose the real winner for any SQL or PL/SQL code that you write.