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: 2806

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;