I use the following oracle query to find out the parent table (or PK table) of the each of the table present in the database.
select user_constraints.table_name, user_cons_columns.table_name from user_constraints, user_cons_columns where user_constraints.constraint_type='R' and user_cons_columns.constraint_name=user_constraints .r_constraint_name order by user_constraints.table_name
This query takes approximately 8 minutes to return in a oracle db containing 300 tables! Anyhelp on optimizing this query ? or is there anyother way to find this (PK tables of all the tables) out ?
I don't understand why you are using user_cons_columns when you are not interested in column names. The other issue is that you need to include OWNER in the joins to get better performance. So try this:
select parent.table_name, child.table_name
from user_constraints parent, user_constraints child
and parent.owner = child.owner
order by parent.table_name, child.table_name
Constraints are uniquely identified by the combination (owner, constraint_name), and query performance suggests that the index is in that order, meaning you need to include owner in the query for it to use the index.