28 | 03 | 2024
Latest Articles
Popular Articles

Scripts

Non indexed foreign keys

User Rating:  / 0
PoorBest 

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. 

 

col column_name format a30
col owner format a30
col table_name format a30
col constraint_name format a30
 
select a.constraint_name,
       b.table_name,
       b.column_name,
       c.num_rows,
       c.blocks
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 b.position = 1
  and a.owner='&OWNER' 
  and (b.table_name,b.column_name) not in
  ( select table_name,
           column_name
    from dba_ind_columns
    where index_owner='&INDEX_OWNER'
      and column_position = 1 )
order by table_name;