Unindexed foreign keys

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

Unindexed foreign keys can lead to bad database performance due to lock contention and full table scans performed on the child table.
Here is a diagram which illustrate the situation:

unindexed foreign keys

In this exemple Oracle needs to lock the entire employees table when the primary key of the departments table is modified, in addition to that if you update many rows of the departments table, a full table scan of the employees table is performed for each different value updated in the departments table.
This is probably not what you want.

Script to find unindexed foreign keys

Here is a great script found in the Tom Kyte Blog to find unindexed foreign keys in a schema.

select table_name, constraint_name,
       cname1 || nvl2(cname2,','||cname2,null) ||
       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
              columns
    from ( select b.table_name,
                  b.constraint_name,
                  max(decode( position, 1, column_name, null )) cname1,
                  max(decode( position, 2, column_name, null )) cname2,
                  max(decode( position, 3, column_name, null )) cname3,
                  max(decode( position, 4, column_name, null )) cname4,
                  max(decode( position, 5, column_name, null )) cname5,
                  max(decode( position, 6, column_name, null )) cname6,
                  max(decode( position, 7, column_name, null )) cname7,
                  max(decode( position, 8, column_name, null )) cname8,
                  count(*) col_cnt
             from (select substr(table_name,1,30) table_name,
                          substr(constraint_name,1,30) constraint_name,
                          substr(column_name,1,30) column_name,
                          position
                     from user_cons_columns ) a,
                  user_constraints b
            where a.constraint_name = b.constraint_name
              and b.constraint_type = 'R'
            group by b.table_name, b.constraint_name
         ) cons
   where col_cnt > ALL
           ( select count(*)
               from user_ind_columns i
              where i.table_name = cons.table_name
                and i.column_name in (cname1, cname2, cname3, cname4,
                                      cname5, cname6, cname7, cname8 )
                and i.column_position <= cons.col_cnt
              group by i.index_name
           );

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.