Faith & Code | Reflections by Errong

A Lesson Learned: Slow DELETE Caused by Missing FK Indexes Recently, I was working on a data purge task. The logic itself was simple: delete old records based on certain conditions. I had done similar work before, so I did not expect any major issues. However, this time the DELETE operation got stuck . No errors were reported, but the SQL just kept running without making progress. What Happened? With help from our DBA, we started investigating the issue at the database level. After some analysis, we identified the root cause: Several foreign key (FK) columns were missing supporting indexes. Because of this, every DELETE on a parent table forced Oracle to perform full table scans on related child tables to validate referential integrity. As data volume increased, the performance impact became severe. The Fix After adding indexes on the missing foreign key columns (across related tables), the results were immediate: DELETE operations completed much faster ...

Safe Data Purge: How We Remove Old Records in Large Oracle Tables At our company, we periodically purge old data to comply with data retention regulations. Deleting millions of rows from production databases requires careful planning. Here's how we do it. Step 1: Catalog and Plan Catalog all tables and identify retention periods. Review retention schedule with legal team. Coordinate with other teams to identify downstream dependencies. Step 2: Backup Tables Always backup affected tables before deleting. This ensures quick recovery in case of unexpected issues. Step 3: Understand Table Structure Identify child tables and parent tables. Delete child tables first to avoid foreign key violations. Step 4: Batch Deletes vs Direct Deletes We tested batch deletes in our PT environment and it worked fine. However, in production: Tables have 7M+ rows and 1M+ rows to delete Batch of 20K rows...