Optimize the Massive DELETE Operation in Oracle, Part 1

Average Rating: 3.6/5 | Rate this item | 29 users have rated this item.

Optimize the Massive DELETE Operation in Oracle, Part 1 (cont'd)

Performing Massive DELETEs as Bulk INSERT
The following steps implement the technique of implementing DELETEs as direct-path INSERTs:

Create a table (TestData_X) with the identical structure as the TestData table in the previous section but with no primary key, alternate key(s), or foreign key(s) constraints (NOLOGGING option).
Insert data that you need to be keep into the newly created table using direct-path INSERT.
Create indexes on the new table with NOLOGGING PARALLEL options.
Create constraints on the new table. Use the ENABLE NOVALIDATE option for referential integrity and check constraints.
Swap the TestData and TestData_X tables: rename TestData to TestData_T, rename TestData_X to TestData.
Restore the original logging settings.
Verify the results and drop the TestData_T table.

Optimize the Massive DELETE Operation in Oracle, Part 2 will present the SwapOps package, which contains all of the APIs necessary to implement the above steps. It also will present performance tests for DELETEs and direct-load INSERT, and compare the two.

Previous Page: Generate Test Data (Scenario 2)

Boris Milrud has more than 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose, CA. He specializes in all aspects of Oracle database software development, including database design, programming, optimization, and tuning.

Page 1: Introduction	Page 3: Generate Test Data (Scenario 2)
Page 2: Need Proof? Generate Test Data	Page 4: Performing Massive DELETEs as Bulk INSERT