A Lesson Learned: Slow DELETE Caused by Missing FK Indexes

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
• Locks were released quickly
• Overall database load was significantly reduced

Same SQL. Same data. Very different outcome.

How We Found the Missing FK Indexes

The DBA used the following query to identify foreign key constraints that did not have corresponding indexes. Schema names, table names, and index names have been anonymized.

WITH fk_constraints AS (
    SELECT
        c.table_name,
        c.constraint_name                                       AS fk_name,
        LISTAGG(c.column_name, ', ')
            WITHIN GROUP (ORDER BY c.position)                  AS fk_columns
    FROM dba_cons_columns c
    JOIN dba_constraints k
        ON k.constraint_name = c.constraint_name
    WHERE k.constraint_type = 'R'
      AND c.owner            = 'APP_SCHEMA_X'
      AND k.owner            = 'APP_SCHEMA_X'
    GROUP BY c.table_name, c.constraint_name
)
SELECT
    fk.table_name,
    fk.fk_name,
    fk.fk_columns,
    idx.index_name              AS existing_index,
    idx.index_columns           AS index_columns,
    'CREATE INDEX ' || fk.fk_name ||
    ' ON ' || fk.table_name ||
    ' (' || fk.fk_columns || ');' AS suggested_fix
FROM fk_constraints fk
LEFT JOIN (
    SELECT
        ic.table_name,
        ic.index_name,
        LISTAGG(ic.column_name, ', ')
            WITHIN GROUP (ORDER BY ic.column_position) AS index_columns
    FROM dba_ind_columns ic
    WHERE ic.index_owner = 'APP_SCHEMA_X'
    GROUP BY ic.table_name, ic.index_name
) idx
    ON idx.table_name = fk.table_name
   AND idx.index_columns LIKE fk.fk_columns || '%'
WHERE idx.index_name IS NULL
ORDER BY fk.table_name, fk.fk_name;

This query:

• Lists all foreign key constraints
• Checks whether an index exists that starts with the FK columns
• Identifies missing indexes
• Generates a CREATE INDEX statement as a suggestion

Key Takeaways

• Oracle does not automatically create indexes for foreign keys
• Missing FK indexes can severely impact DELETE and UPDATE performance
• Issues like this often appear only after data volume grows
• A schema that works is not always a well-designed schema

Looking back, this should have been addressed during the early design or development phase, but it was missed.

Final Thoughts

This was a painful issue, but also a valuable learning experience.

If you encounter DELETE statements hanging or causing unexpected locks, check your foreign key indexes first. It might save you a lot of debugging time.

Hopefully, this helps someone avoid the same mistake.

❤️ Support This Blog

---

If this post helped you, you can support my writing with a small donation. Thank you for reading.

---

Coffee ☕ Thanks 🙏 Extra Support ❤️