To create an index on all foreign keys has two benefits:

  • Reduced risk of table locks.
  • Potentially better performance.

The drawbacks can be neglected. For more information see the discussion in "Ask Tom".

Tom Kyte has published a SQL script to detect unindexed foreign key. It's included beyond with a few amendments to run it in SQL*Plus:

fk_without_index.sql
set linesize 132;
set pagesize 1000;
set echo off;
set feedback off;
set trimspool on;
set heading on;
column columns format a40
column constraint_name format a40
column table_name format a40
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
        )
/

Based upon Toms script, you can easily create another script to build indexes on the unindexed foreign keys using the following code snippet:

fk_create_indexes.sql
set linesize 1024;
set pagesize 0;
set echo off;
set feedback off;
set trimspool on;
set heading off;
spool do_fk_create_indexes.sql
column columns format a40
column constraint_name format a40
column table_name format a40
select 'create index ' || constraint_name 
		|| ' on ' || table_name 
		|| '(' || columns || ');'
from (
	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
        )
  )
/
spool off
set linesize 1024;
set pagesize 1000;
set echo off;
set feedback off;
set trimspool on;
set heading on;
prompt
prompt Now you can:
prompt
prompt ed do_fk_create_indexes.sql
prompt @do_fk_create_indexes.sql
  • No labels