How to find the index on a foreign key

[Sameer]

Originally posted by Nugpot
I'm using, but I am not getting the desired result

SELECT a.owner,a.table_name, a.constraint_name, b.column_name,c.index_name
FROM dba_constraints a, dba_cons_columns b, dba_ind_columns c
WHERE a.owner = 'SVILLAGE2'
and a.constraint_name = b.constraint_name
AND a.constraint_type = 'R'
and a.table_name = c.table_name
and c.column_name != b.column_name
ORDER BY table_name

thanks for the help

You are not getting the desired result because you need OUTER JOIN with the dba_ind_columns view, so that you will get all those foreign keys on which index is not created. And thats why I used outer join for user_ind_columns view and sorted by descending so that you will see index_name NULL for foreign keys where index is missing. Heres your modified query..

SELECT a.owner,a.table_name, a.constraint_name, 
b.column_name,c.index_name
FROM dba_constraints a, 
     dba_cons_columns b, 
     dba_ind_columns c
WHERE  a.owner = 'SVILLAGE2'
AND a.constraint_name = b.constraint_name
AND a.constraint_type = 'R'
and a.table_name = c.table_name
and b.column_name = c.column_name(+)
ORDER BY table_name

HTH

Sameer