How to find the index on a foreign key

Hi,

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

Thanks for your help
Vj

Code:

---

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

---

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

Thanks

post your query

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

Quote:

---

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

Code:

---

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

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
/

Quote:

---

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

Code:

---

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

---

Quote:

---

Originally posted by Nugpot

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

Thanks

---

Code:

---


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.

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

Vj