-
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
-
-
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
-
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
/
-
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
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thank you all for the responses. It helped me a lot
Vj
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|