Oracle – How to improve delete performance
Problem :
I was asked to delete the 1 Million rows from the table and I wanted make sure that it take bare minimum resources and time.
Solution :
I tried all possible solutions on my test system and here are findings.
I had created a test table with 100000 rows with one index on top of it.
SQL> select count(*) from custaccounts; COUNT(*) ---------- 1000000 SQL> select index_name from user_indexes where table_name ='CUSTACCOUNTS'; INDEX_NAME ------------------------------ CA_U1 CA_PK SQL> select num_rows from user_indexes where table_name ='CUSTACCOUNTS'; NUM_ROWS ---------- 1017005 1013011 Elapsed: 00:00:00.13
- DELETE NORMALLY:
SQL> delete from CUSTACCOUNTS; 1000000 rows deleted. Elapsed: 00:35:08.93
This operation had generated 17 Archive logs.
- DELETE WITH NOLOGGING:
Most of the people thinks that if they perform delete with nologging , it will have good performance improvement because nologging will not create any redo.Its not true guys.let me prove my point with demo.
SQL> delete from custaccounts nologging; 1000000 rows deleted. Elapsed: 00:34:09.29
This operation had generated 16 Archive logs.I even tried the rollback operation as well and it went through fine.
- DELETE PARALLEL:
Next I thought of doing delete in parallel.
SQL>alter session enable parallel dml; session altered. SQL> delete from custaccounts;
1000000 rows deleted. Elapsed: 00:34:05.29
This operation had generated 16 archive logs.
- DELETE INDEX and DELETE ROWS:
Now I tried with deleting the indexes of the table first and than tried the delete operation.I could see good performance improvement with this method.
SQL> drop index CA_U1; Index dropped. Elapsed: 00:00:00.75 SQL> select index_name from user_indexes where table_name ='CUSTACCOUNTS'; INDEX_NAME ------------------------------ CA_PK Elapsed: 00:00:00.17 SQL> delete from custaccounts; 1000000 rows deleted.
Elapsed: 00:23:30.71
This operation had generated 12 archive logs.
- CONCLUSION:
After performing delete operation with different option, I came to following conclusion.
Operation Duration Archive logs Recommended DELETE 00:35:08.93 17 No DELETE NOLOGGING 00:34:09.29 16 No DELETE PARALLEL 00:34:08.00 16 No DELETE WITH NO INDEX 00:23:30.71 12 Yes
If you are deleting lot of rows, CTAS is best approach.
cheers
Amit