RE: Slow query

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 18 Aug 2004 12:11:20 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F879D7@irvmbxw02>

You could at least add the condition "and owner not in ('SYS', 'SYSTEM')"
Or if you can narrow down the number of schemae involved to one or two, then
"and owner in ('SCHEMA1', 'SCHEMA2')"
-----Original Message-----
Thomas Day

Thanks. However, I'm not worried about table owner. Rarely, but possibly,
a column could be foreign keyed to a column in a table in another schema.

From: "Jacques Kilchoer"

First, clean up your query - you have an unnecessary view in your from clause (cartesian join on dba_cons_columns in the outer query) and the joins are incomplete (don't you want to join on table owner?). select
  a.table_name, a.column_name
from
  dba_tab_columns a, dba_tab_columns b
where
  a.owner = '&owner'

  and b.owner = a.owner -- (?) I imagine you want this condition
  and a.column_name like '%' || b.column_name || '%'
  and a.column_name != b.column_name

does the b column have to be in a different table than the a column?
and b.table_name != a.table_name and not exists (select null from dba_cons_columns c where c.owner = b.owner and c.table_name = b.table_name and c.column_name = b.column_name) ;

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Aug 18 2004 - 14:07:25 CDT