Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Optimize the Massive DELETE Operation in Oracle, Part 2

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.


advertisement


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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap