doc

    Name:    fkeys.sql

    Author:  Mark Gurry

    This script lists all foreign keys that currently exist in the 
    database without the foreign key columns indexed in the child table.
    Not having the index can cause shared locking problems on the 
    parent table.

#


ttitle ' Foreign Constraints and Columns Without an Index on Child Table'
select acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
	    ||'['||acc.position||'])'||' ***** Missing Index' 
  from		all_cons_columns acc, all_constraints ac
 where  ac.constraint_name = acc.constraint_name
   and		ac.constraint_type = 'R'
   and		(acc.owner, acc.table_name, acc.column_name, acc.position) in
 (select acc.owner, acc.table_name, acc.column_name, acc.position 
    from   all_cons_columns acc, all_constraints ac
		where  ac.constraint_name = acc.constraint_name
				and   ac.constraint_type = 'R'
	MINUS
	select table_owner, table_name, column_name, column_position
	  from all_ind_columns)
order by acc.owner, acc.constraint_name, acc.column_name, acc.position;