RE: Slow query

From: Thomas Day <tday6_at_csc.com>
Date: Wed, 18 Aug 2004 08:50:55 -0400
Message-ID: <OF1177476B.D5B42F11-ON85256EF4.00467AB5@csc.com>

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.

                                                                                                                                       
                      "Jacques                                                                                                         
                      Kilchoer"                To:      <oracle-l_at_freelists.org>                                                       
                      <Jacques.Kilchoe         cc:                                                                                     
                      r                        Subject: RE: Slow query                                                                 
                      @quest.com>                                                                                                      
                      Sent by:                                                                                                         
                      oracle-l-bounce                                                                                                  
                                                                                                                                       
                                                                                                                                       
                      08/16/2004 08:52                                                                                                 
                      PM                                                                                                               
                      Please respond                                                                                                   
                      to oracle-l

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) ;

-----Original Message-----
Thomas Day

I'm trying to find all the columns, in tables owned by a given schema, where the column name is like any other column name but not a constrained
column. It takes forever. Any ideas on how to speed this up? It's Oracle
9.2.

select /* FIRST ROW */ UNIQUE a.table_name, a.column_name from dba_tab_columns a, dba_tab_columns b,
dba_cons_columns c
where a.column_name like '''%'||b.column_name||'%''' and a.column_name not
in
(select /* RULE */ c.column_name from dba_cons_columns c) AND A.COLUMN_NAME != B.COLUMN_NAME and a.owner = 'owner' /

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
-----------------------------------------------------------------




----------------------------------------------------------------
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 - 07:49:53 CDT