Indexing referenced column of a table

Most of the databases, foreign Keys needs to be indexed otherwise deleting records from parent table will lock down child table(s). Sometimes this may also lead to a extensive locking and even deadlock situation!


Here is a convenient script I needed once to index all referenced columns of a table - say MEDICAL_CONTACT.


set pagesize 0
set linesize 200
SPOOL medical-contact-fk-index.sql


SELECT  'CREATE INDEX ' || a.table_name || '_' || c.column_name || '_I ON ' || a.table_name || '(' || c.column_name
|| ') ONLINE TABLESPACE INDX01_16K COMPUTE STATISTICS;'
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND a.constraint_type='R'
AND b.table_name='MEDICAL_CONTACT';        

SPOOL OFF;


Might needs to modify medical-contact-fk-index.sql to change index names if cross max length for names.

--