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,
p.constraint_name Parent_Constraint,
co2.column_name Parent_Column_Missing_Data,
c.table_name Child_Table,
c.constraint_name Child_Constraint,
co.column_name Child_Column_In_Violation,
count(*) Invalid_Row_Count
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!