Dealing with Unindexed Foriegn Keys

Couple of months back we were hitting a lot of deadlock issues. I found out that deadlocks occur due to unindexes foreign keys and found the following script to find and create unindexed foriegn keys.

Using SQL*Plus, connect to the schema for which you need to find the unindexed foriegn keys. Then execute the following scripts:

Script to find Unindexed Foriegn Keys

column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped

select table_name, constraint_name,
cname1 PIPE-PIPE nvl2(cname2,',' PIPE-PIPE cname2,null) PIPE-PIPE
nvl2(cname3,',' PIPE-PIPE cname3,null) PIPE-PIPE nvl2(cname4,',' PIPE-PIPE cname4,null) PIPE-PIPE
nvl2(cname5,',' PIPE-PIPE cname5,null) PIPE-PIPE nvl2(cname6,',' PIPE-PIPE cname6,null) PIPE-PIPE
nvl2(cname7,',' PIPE-PIPE cname7,null) PIPE-PIPE nvl2(cname8,',' PIPE-PIPE 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
);

Script to generate 'CREATE' statements for Unindexed Foriegn Keys

create global temporary table temp_index
(commande varchar2(500))
on commit delete rows;

define Tablespace_name = &&Tablespace_name
set feedback off
set linesize 255
set serveroutput on
set verify off
set heading off;
declare
L_nom_colo varchar2(2000);

Cursor sel_cons is
Select constraint_name, table_name
from user_constraints
where constraint_type = 'R';

Cursor sel_colo(P_nom_cons user_cons_columns.constraint_name%type,
P_nom_tabl user_cons_columns.table_name%type) is
Select column_name
from user_cons_columns
where constraint_name = P_nom_cons
and table_name = P_nom_tabl
order by position;

begin

for liste_cons in sel_cons loop
L_nom_colo := null;
for liste_colo in sel_colo(liste_cons.constraint_name, liste_cons.table_name) loop
if L_nom_colo is not null and liste_colo.column_name is not null then
L_nom_colo := L_nom_colo PIPE-PIPE ',';
end if;
L_nom_colo := L_nom_colo PIPE-PIPE liste_colo.column_name;
End loop;

insert into temp_index values('Create index ' PIPE-PIPE liste_cons.constraint_name PIPE-PIPE ' on '
PIPE-PIPE liste_cons.table_name PIPE-PIPE '(' PIPE-PIPE L_nom_colo PIPE-PIPE ') tablespace &Tablespace_name;');
end loop;
end;
/
spool &SCRIPT_NAME
select * from temp_index;
spool off
drop table temp_index;

A deadlock means that process X has a lock on resource 1 and is waiting for resource 2, while process Y has a lock on resource 2 and is waiting to acquire a lock on resource 1.