How to find the index on a foreign key

[Nugpot]

Hi,

How would you find if a foreign key is indexed or not by looking at v$views?

Thanks for your help
Vj

[Sameer]

SELECT a.table_name, a.constraint_name, b.column_name, c.index_name
FROM user_constraints a, user_cons_columns b, user_ind_columns c
WHERE a.constraint_name = b.constraint_name
AND b.column_name = c.column_name(+)
AND constraint_type = 'R'
ORDER BY 4 DESC

[Nugpot]

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

Thanks

[pando]

post your query

[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

[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

[mallika]

Use the below query


select column_name,table_name,position from user_cons_columns where constraint_name in
(select constraint_name from user_constraints where constraint_type='R')
minus
select column_name,table_name,column_position from user_ind_columns
/

[abhaysk]

Originally posted by Sameer
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
Sameer

Just to add...if u want o/p more readable....

SELECT a.owner,a.table_name, a.constraint_name, 
b.column_name,NVL(c.index_name,'Not Indexed')
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

[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.

[Nugpot]

Thank you all for the responses. It helped me a lot

Vj