How to find the index on a foreign key

[abhaysk]

Originally posted by Nugpot

Can you help me with the query to get all the foreign keys which are NOT indexed.

Thanks

 
Select 
b.owner                , 
b.table_name          , 
b.constraint_name    , 
b.column_name       ,  
b.index_name 
from
(
SELECT a.owner,a.table_name, a.constraint_name,
b.column_name,NVL(c.index_name,'Not_Indexed') 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
) b
Where b.index_name='Not_Indexed'
/

Regards
Abhay.