21 | 10 | 2018
Latest Articles
Popular Articles


Non indexed foreign keys

User Rating:  / 0

This is a script I wrote some time ago. It lists which foreign key columns are not indexed. Note that it is common pratice to create indexes on foreign key columns, at least when both parent and child tables are subject to concurrent DML, a B-tree index should be created on the foreign key column. 


select a.constraint_name,
from dba_constraints a,
     dba_cons_columns b,
     dba_tables c 
where b.table_name=c.table_name
  and a.constraint_name=b.constraint_name 
  and a.constraint_type='R' 
  and a.owner=b.owner 
  and a.owner=c.owner 
  and a.owner='&OWNER' 
  and (b.table_name,b.column_name) not in
  ( select table_name,
    from dba_ind_columns
    where index_owner='&INDEX_OWNER'
      and column_position = 1 )
order by table_name;