Optimize the Massive DELETE Operation in Oracle, Part 2

y previous 10-Minute Solution "Optimize the Massive DELETE Operation in Oracle, Part 1" described the technique for performing a high-performance massive DELETE operation as a direct-path INSERT. At the end of the article, I outlined six steps for implementing this technique:

• Step 1
  Create a table (TestData_X) with identical structure to an existing table (TestData) but without primary key, alternate key(s), and foreign key(s) constraints. Create it with the NOLOGGING option.
• Step 2
  Insert the data you need to keep into the newly created table using a direct-path INSERT.
• Step 3
  Create indexes on the new table with NOLOGGING PARALLEL options.
• Step 4
  Create constraints on the new table. Use the ENABLE NOVALIDATE option for referential integrity and check constraints.
• Step 5
  Swap the TestData and TestData_X tables: rename TestData to TestData_T, and rename TestData_X to TestData.
• Step 6
  Restore the original logging settings. Verify the results and drop the TestData_T table.

As you can see, the steps are rather general. You could automate them by creating a set of APIs that accepts the table name as a parameter. I developed a SwapOps package that delivers the functionality of the above six steps.

How do I complete a massive DELETE operation in my Oracle database without having to pay the heavy performance overhead?

Perform the massive DELETE operation as a swap delete with parallel execution. This technique can offer significant performance gains over a straight or in-place delete operation.

Next Page

• Download The Code for this Article