If you are involved in a project based on a critical and extensively used database, or if you are involved in stress and load testing, data maintenance is an issue you'll come across sooner or later.

When you execute a simple DELETE FROM ... WHERE... and the WHERE clause involves a large number of rows, a problem that frequently arises is the Database runs out of space for the transaction buffer. In these situations you can apply the following two step process:

  • Obtain the rows to delete
  • Delete the rows one by one, while limiting the number of rows that are included within each transaction

The following example uses the Oracle Database.

The generation of the delete statements may be quite expensive depending on the WHERE condition involved. However the generated deletes contain WHERE conditions that use ROWID and as such are much faster to execute.

Step 1: Obtain the rows to delete

This is carried out by redirecting the output (spool in SQLPlus) of a script like this one to an sql file. Note: This script does not contain the batch blocks yet.

prompt prompt DELETE user.table_name ...
SELECT  'DELETE user.table_name WHERE rowid='''||table_name.rowid||''';'
FROM user.table_name, ...
WHERE ... -- complex condition;

Preparing the batch blocks

You can do this with the MOD and DECODE functions, as follows.

prompt prompt DELETE user.table_name ...
SELECT 'DELETE user.table_name 
WHERE rowid='''||table_name.rowid||''';'||
    DECODE(MOD(ROWNUM,1000),0,CHR(10)||CHR(13)||'COMMIT;',NULL)
FROM user.table_name, ...
WHERE ... -- complex condition;
prompt COMMIT;

The DECODE function implements an if-like condition so that when the remainder of ROWNUM divided by 1000 is zero then a COMMIT line is inserted, which automatically starts a new transaction (in this case we insert a COMMIT every 1000 lines but this can be tuned as needed). An example of the sql that is produced would be:

...
DELETE user.table_name WHERE rowid='AAAAACAABAAATNWAAB';
DELETE user.table_name WHERE rowid='AAAJWTAAWAAB+BiAAC';
COMMIT;
DELETE user.table_name WHERE rowid='AAAKPkAAKAAARs7AAC';
... 

Step 2: Delete the rows one by one

Execute the sql file resulting from step 1.

Filed under: languages, infrastructure

comments

There are no comments.