RE: Slow query
- From: Thomas Day <tday6@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 18 Aug 2004 08:50:55 -0400
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@xxxxxxxxxxxxx>
<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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
