RE: Slow query

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 16 Aug 2004 17:52:50 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FC40@irvmbxw02>

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

Received on Mon Aug 16 2004 - 19:48:29 CDT