Home > Oracle, Performance, Performance Analysis, SQL > Indexes on Foreign Keys

Indexes on Foreign Keys

Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed. Below query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.

SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
 ||'['||acc.position||'])'||' ***** Missing Index'
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 AND (acc.owner, acc.table_name, acc.column_name, acc.position)
 IN
 (SELECT acc.owner, acc.table_name, acc.column_name, acc.position
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 MINUS
 SELECT table_owner, table_name, column_name, column_position
 FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
 acc.column_name, acc.position;

By creating an index on the foreign key of the child table,  “table-level” locks can be avoided.

Keep in mind that you will often be creating an index on the foreign keys in order to optimize join and queries. However, if you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention. If ever in doubt, it’s often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.

Source / Reference links:

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: