RE: Slow query
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Aug 2004 17:52:50 -0700
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@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
-----------------------------------------------------------------
Other related posts:
