FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE

 

FK Index Problems

FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:

Owner - Owner of the table

Constraint Name - Name of the constraint

Column Name - Name of the column

Position - Position of the index

Problem - Nature of the problem

It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.

select  acc.OWNER,
 acc.CONSTRAINT_NAME,
 acc.COLUMN_NAME,
 acc.POSITION,
 'No Index' Problem
from    dba_cons_columns acc, 
 dba_constraints ac
where   ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
and    ac.CONSTRAINT_TYPE = 'R'
and     acc.OWNER not in ('SYS','SYSTEM')
and     not exists (
        select  'TRUE' 
        from    dba_ind_columns b
        where   b.TABLE_OWNER = acc.OWNER
        and     b.TABLE_NAME = acc.TABLE_NAME
        and     b.COLUMN_NAME = acc.COLUMN_NAME
        and     b.COLUMN_POSITION = acc.POSITION)
order   by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION