DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to find the index on a foreign key

  1. #1
    Join Date
    Feb 2001
    Posts
    129

    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

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  3. #3
    Join Date
    Feb 2001
    Posts
    129
    Can you help me with the query to get all the foreign keys which are NOT indexed.

    Thanks

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    post your query

  5. #5
    Join Date
    Feb 2001
    Posts
    129
    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

  6. #6
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  7. #7
    Join Date
    May 2002
    Posts
    27
    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
    /

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  10. #10
    Join Date
    Feb 2001
    Posts
    129
    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
  •  


Click Here to Expand Forum to Full Width