We're moving a schema from one machine/DB to another. Some new constraints are being added to some tables, and some of the rows in the child table don't have associated parents (they're orphans). In order to understand which columns, tables and constraints are the problems, I have done the following:
run utlexcpt.sql to create the exceptions table
enabled the offending constraint with exceptions into exceptions
developed a query to extract table, column and constraint info from the data dictionary based upon the contents of the exceptions table.
The problem is, this query runs for several minutes on a quad processor machine with no other users attached. The exceptions table contains about 10,000 rows from seven user tables.
How can this query be improved?
select p.table_name Parent_Table,
from dba_constraints p, dba_constraints c, exceptions e, dba_cons_columns co, dba_cons_columns co2
where p.constraint_Name = c.r_constraint_name and
c.constraint_name = e.constraint and
co.constraint_name = c.constraint_name and
co2.constraint_name = c.r_constraint_name
group by p.table_name, p.constraint_name, co2.column_name, c.table_name, c.constraint_name, co.column_name
order by p.table_name, co2.column_name, c.table_name, c.constraint_name
I could get rid of the "order by" . . . any other suggestions?
Thanks in advance!
Click Here to Expand Forum to Full Width