Database Programer / DBA has to test all the case in TEST Environment they have to Test below existing methods and find one that best suite for deletion
- Test Case 1 – Basic Delete
- Test Case 2– Delete with PLSQL and forall loop, committing only once
- Test Case 3 – Delete using anonymous PL/SQL block, looping through the cursor, and deleting one tow at a time. Loop has counter, programmed to trigger commit after each 1000 records.
- Test Case 4 – a.) Deleting using PL/SQL stored procedure, commit each 1000 records & b.) Deleting using PL/SQL stored procedure, commit only once.
- Test Case 5 – Delete using standard anonymous PL/SQL block, using execute immediate function and committing only once.
We can get the best result by EXECUTE_IMMEDIATE calls inside an anonymous PL/SQL block, (i.e. test case 5) This is not surprising, since Oracle suggests this method for mass data deletion.
Important recommendations to speed a delete operation:
- Do not delete all the data in one shot ; break data into chunks and commit in between .
- delete indexes before and recreate after.
- for tables with many deletes, try to avoid bitmap indexes which cause locking problems and performance degradation .
- use parallel DML when possible .
- Check for index on the FK columns, which have to be locked when a row is deleted from the parent (each row deleted in the Master is causing a full table scan in one or both of the cascaded tables.) .
- deleting using ROWID (fetching the entire ROWID result set into a cursor).
Potential problems due to mass delete action is rising CPU usage and disk I/O.
