Link to home
Start Free TrialLog in
Avatar of sdruss
sdruss

asked on

Need SQL Query to Find Foreign-keys Without Indexed Columns

I need to make sure that all of my foreign-key relationships are indexed in my Oracle Database.  Believe by joining user_cons_columns, user_contraints, user_index this can be accomplished?  Need a really simple query to identify foreign keys without indexes.  Thanks.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Off the top of my head, I'd start with something like:
select *
  from user_cons_columns b,
       user_constraints a
 where a.constraint_type = 'R'
   and b.owner = a.owner
   and b.constraint_name = a.constraint_name
   and not exists (select 'X'
          from user_ind_columns x
         where x.table_name = a.table_name
           and x.column_name = b.column_name);

Open in new window


But the provided links from slightwv should do the same ;-)
ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial