Unindexed foreign keys

Unindexed foreign keys can lead to bad database performance due to lock contention and full table scans performed on the child table.
Here is a diagram which illustrate the situation:

Unindexed foreign keys

In this exemple Oracle needs to lock the entire employees table when the primary key of the departments table is modified, in addition to that if you update many rows of the departments table, a full table scan of the employees table is performed for each different value updated in the departments table.
This is probably not what you want.

Script to find unindexed foreign keys

Here is a great script found in the Tom Kyte Blog to find unindexed foreign keys in a schema.

Leave a Reply