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.