Hi,
How would you find if a foreign key is indexed or not by looking at v$views?
Thanks for your help
Vj
Printable View
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
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..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
HTHCode: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
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
/
Just to add...if u want o/p more readable....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
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
RegardsCode:
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'
/
Abhay.
Thank you all for the responses. It helped me a lot
Vj