Scripts
Non indexed foreign keys
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Wednesday, 17 March 2010 22:38
-
Last Updated on Tuesday, 12 November 2019 15:41
-
Published on Wednesday, 17 March 2010 22:38
-
Hits: 2790
This is a script I wrote some time ago. It lists which foreign key columns are not indexed. Note that it is common pratice to create indexes on foreign key columns, at least when both parent and child tables are subject to concurrent DML, a B-tree index should be created on the foreign key column.
col column_name format a30
col owner format a30
col table_name format a30
col constraint_name format a30
select a.constraint_name,
b.table_name,
b.column_name,
c.num_rows,
c.blocks
from dba_constraints a,
dba_cons_columns b,
dba_tables c
where b.table_name=c.table_name
and a.constraint_name=b.constraint_name
and a.constraint_type='R'
and a.owner=b.owner
and a.owner=c.owner
and b.position = 1
and a.owner='&OWNER'
and (b.table_name,b.column_name) not in
( select table_name,
column_name
from dba_ind_columns
where index_owner='&INDEX_OWNER'
and column_position = 1 )
order by table_name;