Optimize the Massive DELETE Operation in Oracle, Part 2 : Page 3

A SwapOps Demonstration
This section shows an example of how to use the SwapOps package. In "Optimize the Massive DELETE Operation in Oracle, Part 1" I created a table (TestData) with 500K+ rows that was logically partitioned by values in a runId column. If you run the following query:

select runId, count(*)
  from TestData
 group by runId;

You'll get the following output:

     RUNID   COUNT(*)
---------- ----------
         1      20496
         2      20496
         3      20496
         4      20496
         5      20496
         6      20496
         7      20496
         8      20496
         9      20496
        10      20496
        11      20496
        12      20496
        13      20496
        14      20496
        15      20496
        16      20496
        17      20496
        18      20496
        19      20496
        20      20496
        21      20496
        22      20496
        23      20496
        24      20496
        25      20496

Delete the data produced by the first 20 runs—rows with runId values between 1 and 20. The code for this is very simple: just one call of the FullInsert()procedure with two parameters, tableName ('TestData'), and a WHERE clause string for rows you want to keep ('runId > 20'):

begin
   SwapOps.FullInsert('TestData', 'runId > 20');
end;
/

Now, run the first query again:

select runId, count(*)
  from TestData
 group by runId;

You'll get this output:

     RUNID   COUNT(*)
---------- ----------
        21      20496
        22      20496
        23      20496
        24      20496
        25      20496

As you can see, the data from the first 20 runs is gone. The default value for the pDrop parameter FullInsert()procedure is 'no'. That means the original table you renamed TestData_T was not dropped. For safety reasons, you may want to verify the results first and then drop it.

Now you can compare the performance of a straight or in-place delete operation to that of a swap delete.

Previous Page

Next Page

• Download The Code for this Article