No one gets a maintenance window for days. The standard maintenance window is 4 hours or less for most companies. There are however application which can't be down even for a single full second. Cable, ISP, Power, Communication, eCommerce, Financial companies are few examples that are up 24/7/367. Imagine the financial impact on Amazon may have when you need to take down the application for maintenance for hours. Delete/Insert are part of the maintenance and they should NOT take days to load or purge therefore, it is a nice to make those maintenance window short and small If these maintenance can't be done ONLINE.These are the tips I use to make the DELETE perform faster and not take days.
  1. Disable Constraints especially Foreign Keys (FK)
  2. Drop Index(s)
  3. Disable Trigger(s)
  4. Delete using Parallel Hint
  5. Use rowid pseudo-column on WHERE condition where applicable. rowid is much faster compared to Index search.
  6. Use COMMIT interval on large data set for delete. Best Practices not a performance improvement.
  7. Do NOT drop or disable Primary Key (PK).
  8. Ensure Primary Key (PK) is VALID and the Index is not fragmented.
  9. Table is Analyzed with up-to-date Statistics
  10. Use EXISTS instead of IN/NOT IN clause
  11. Use plain SQL and avoid PL/SQL
  12. Use BULK/COLLECT/FORALL Where Pl/SQL is required.
  13. BULK DELETE when DELETE requires multiple SQL statements.
  14. Use DROP partition, if DELETE records belong to a partition of  a table.
  15. Much faster of all is NOT to delete it all. Let the database grow and prosper.
These tips have worked for me. I hope they do wonder on your DELETE as well. If there are better tips that I missed or any questions on these, please comment below.

Interested in working with me? I can be reached at pbaniya04[at] for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

1 comment

Powered by Blogger.