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

In-place Delete and Rebuilding of Indexes
In order to make a fair comparison between in-place delete and swap delete, you have to consider one additional step after an in-place delete: rebuilding indexes. In my experience as an Oracle database developer, I've seen some cases where massive delete operations severely affected indexes and the only way to resolve the problem and get indexes back in shape was index rebuilds. (I described such a case in the "Amazing Results" section of the 10-Minute Solution "Detecting and Fixing Out-of-Balance Indexes".) Because of that, a truly fair comparison for massive delete operations would be in-place delete plus index rebuild vs. swap delete. Keep in mind that a swap delete does not need to rebuild indexes because it creates them from scratch during Step 3. That makes swap delete even more attractive and moves the threshold number considerably lower. I found that if you need to rebuild indexes after an in-place delete, then a swap delete outperforms an in-place delete—even if you need to delete just 10 percent of the rows in the table (See Table 4 for these results).

Using Parallel Execution with Swap Delete
I mentioned earlier in this article that as part of the swap delete process indexes get rebuilt one after another. You can improve that by rebuilding indexes in parallel using Oracle's native DBMS_JOB package. (See my 10-Minute Solution "Run Oracle Stored Procedures in Parallel—Inside the Database" for more details.) However, I've chosen to use parallel execution per table granularity, executing a swap delete operation for each table in parallel. I had a bad performance problem: one batch process deleted data from the previous runs of 20+ tables—one after another. To illustrate the solution, create four clones of TestData table (TestData1 … TestData4). To save testing time, each table has a primary key and only one non-unique index.

First, execute the old code scenario: an in-place delete, one table after another. This listing shows the old code:

delete
   from TestData1
  where mod(runId, 2) = 0;

commit;


delete
   from TestData2
  where mod(runId, 2) = 0;

commit;

delete
   from TestData3
  where mod(runId, 2) = 0;

commit;

delete
   from TestData4
  where mod(runId, 2) = 0;

commit;

Execution time: 148.67 sec.

For that article, I created a SwapDemo package with just one procedure (Delete) that accepted table name as a parameter. The code listing uses the JobPkg package's SignalCompletion API (described in "Run Oracle Stored Procedures in Parallel—Inside the Database").

The following is the SwapDemo package specification:

create or replace package SwapDemo is

   procedure Delete
   (
      pTable varchar2
   );

end SwapDemo;
/

The following is the SwapDemo package body:

create or replace package body SwapDemo is

   procedure Delete
   (
     pTable varchar2
   )
   is
   begin
      SwapOps.FullInsert(pTable, 'MOD(RUNID, 2) > 0');
      JobsPkg.SignalCompletion('SwapDemo.Delete(''' || pTable || ''')');
   end Delete;

end SwapDemo;
/

Now you are ready to test the new solution, which combines the swap delete technique with the power of parallel execution.

The Proof Is in the Performance
The following listing shows a PL/SQL code block that demonstrates a combination of the swap delete technique and parallel execution.

declare
   vJobs JobsPkg.tArrayJob;
begin
   vJobs(1) := 'SwapDemo.Delete(''TestData1'')';
   vJobs(2) := 'SwapDemo.Delete(''TestData2'')';
   vJobs(3) := 'SwapDemo.Delete(''TestData3'')';
   vJobs(4) := 'SwapDemo.Delete(''TestData4'')';

   JobsPkg.Execute(vJobs);      
   JobsPkg.WaitForCompletion(vJobs);
end;
/

The above block produced the same data results as the in-place delete code from the "old code" listing, but with 88 percent performance gain. Execution time was down to 18.47 seconds from 148.67 seconds. The real-life original code was more complicated, but switching to the swap delete technique with parallel execution produced a significant performance gain: for 20+ tables with millions of rows and 50 percent of data to delete, the execution time went down from 20+ hours to just 22 minutes. That clearly shows the solution for a high-performance massive delete operation.

Previous Page

• Download The Code for this Article