Show foreign keys without index

Oracle 9 does not create automaticaly index for foreign key. Therefore use this select to find such cases:


select /*+NO_MERGE*/c.TABLE_NAME, c.CONSTRAINT_NAME, col.COLUMN_NAME
from all_constraints c, all_cons_columns col
where c.owner = 'RMS'
and c.constraint_name not like 'SYS_%'
and c.constraint_type = 'R'
and c.constraint_name = col.constraint_name
and not exists (select i_col.COLUMN_NAME
from all_indexes i, all_ind_columns i_col
where i.table_name = c.TABLE_NAME
and i.owner = c.OWNER
and i_col.index_nameĀ  = i.INDEX_NAME
and i_col.COLUMN_NAME = col.COLUMN_NAME)
order by c.TABLE_NAME

Leave a Reply

Your email address will not be published. Required fields are marked *