Re: Slow query
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 16 Aug 2004 14:01:16 -0500
Thomas,
I hope you don't mind a somewhat generic reply on this - I don't have the
specific answer for this query, offhand, but I have an approach that has worked
for me every time:
These dictionary tables are really views, visible through dba_views and
all_views. They are usually pretty complex, and most of the complexity turns
out to be unnecessary in any specific query such as you have put together,
involving, for example, joins to sys.user$ to find the *names* of the owners of
both tables being compared, which just aren't necessary. (You need only find
the ownerid once, then restrict on that for the joined tables.) Every single
time I try to tune such a query, I find that there exists an equivalent (but
much simpler, in terms of the underlying tables) query directly against the
sys.xxx$ tables that runs at least an order of magnitude faster, and I can
deduce what that query needs to be by looking over the view definitions for the
dba_ views. The only real trick with using these underlying sys.xxx$ tables is
that you have to get the DBAs to grant select on them to you, if you aren't
already a DBA. There's no better way to experiance the dangers and frustrations
of tuning SQL against views than to build complex queries against the
dba_views.
BTW, apart from the common generic problem of using the dba_views for stuff like
this, you have some specific problems with your query:
The alias c in the outer query is unused anywhere else in the query, so it is
combined unnecessarily with the other rowsources in a cartesian product. This
has no functional effect since you have UNIQUE in your select, but it leads to
a *huge* slowdown!
The alias b is unrestricted on owner, but if I understand correctly, you only
want to compared table columns *within* a schema - this will both slow the
query and cause funcitonal differences versus what you're looking for. The same
goes for the alias c inside the subquery.
NOT IN is usually better replaced with the equivalent correlated NOT EXISTS
subquery condition.
Thanks,
Dan Tow
650-858-1557
www.singingsql.com
Quoting Thomas Day <tday6@xxxxxxx>:
>
> 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:
