RE: Slow query

From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 18 Aug 2004 12:11:20 -0700

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

RE: Slow query

Other related posts: