Friday, November 19, 2010

SQL Sinppet : Foreign Key Columns not Inside an Index

--select 'create index '||child_table||'_aidx on '||child_table||'('||child_table_column||') Tablespace Tbxindexes Online Nologging Compute Statistics;' from (
SELECT p.table_name AS parent_table,
pc.column_name AS parent_table_column,
C.Table_Name AS Child_Table,
Cc.Column_Name AS Child_Table_Column ,
C.R_Constraint_Name As Parent_Constraint_Name,
C.Constraint_Name As Child_Constraint_Name,
FROM user_constraints p,
user_constraints c,
user_cons_columns pc,
user_cons_columns cc
WHERE p.constraint_name = c.r_constraint_name
AND p.constraint_name = pc.constraint_name
AND pc.position = cc.position
And C.Constraint_Name = Cc.Constraint_Name
And C.Table_Name = '<<child_table_name_here>>'
--And C.Table_Name like '<<child_table_name_here>>_%'
And Not Exists (Select 1 from user_ind_columns Inds
Where Inds.Table_Name = C.Table_Name
And Inds.Column_Name=Cc.Column_Name
And Inds.Column_Position = Cc.Position)
Order By Parent_Table,child_table,child_constraint_name, Position;
Useful in situations where foreign keys need to be indexed.