Detect Unindexed Foreign Keys

Unindexed foreign keys continue to be a prime performance bottleneck.

Here is a script that will run through the foreign keys in a schema and display them all and the index that is associated with them (or not) and indicate whether the foreign key requires an index.

set lines 140
set pages 1000

col status for a6
col child_table for a24
col fk_columns for a27
col indexed_columns for a27
col parent_table for a24
col pk_columns for a27

select decode( b.table_name, NULL, '****', 'ok' ) Status
     , a.table_name                               Child_Table
     , c.table_name                               Parent_Table
     , c.columns                                  PK_Columns
     , a.columns                                  FK_Columns
     , b.columns                                  Indexed_Columns
from
( select a.table_name
       , a.constraint_name
       , b.r_constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by a.table_name
          , a.constraint_name
          , b.r_constraint_name ) a,
( select table_name
       , index_name
       , max(decode(column_position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_ind_columns
   group by table_name
          , index_name ) b,
( select a.table_name
       , a.constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type in ( 'P' , 'U' )
   group by a.table_name
          , a.constraint_name ) c
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
  and a.r_constraint_name = c.constraint_name
order by a.table_name
/

You will need quite a wide screen to incorporate all the data. I use 140 character width.