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.
Off the top of my head, I'd start with something like:
But the provided links from slightwv should do the same ;-)
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);
But the provided links from slightwv should do the same ;-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For example:
http://www.dba-oracle.com/t_find_foreign_keys_with_no_index.htm
http://www.toadworld.com/platforms/oracle/w/wiki/4918.script-to-report-foreign-keys-with-no-index-on-child-table