Tag: Foreign Key

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:

Find Foreign Key Relationships

The below query lists all of the foreign keys and the parent table and columns to which they relate.

SELECT a.owner , a.table_name , c.column_name ,
 b.owner , b.table_name , d.column_name 
FROM dba_constraints a, dba_constraints b,
 dba_cons_columns c, dba_cons_columns d
WHERE a.r_constraint_name = b.constraint_name
 AND a.constraint_type = 'R'
 AND b.constraint_type = 'P'
 AND a.r_owner=b.owner
 AND a.constraint_name = c.constraint_name
 AND b.constraint_name=d.constraint_name
 AND a.owner = c.owner
 AND a.table_name=c.table_name
 AND b.owner = d.owner
 AND b.table_name=d.table_name;

Source