Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Slow query

Re: Slow query

From: Dan Tow <dantow_at_singingsql.com>
Date: Mon, 16 Aug 2004 14:01:16 -0500
Message-ID: <1092682876.4121047cf1b6a@www.singingsql.com>


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_at_csc.com>:

>
> 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 - 13:58:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US