Thanks. I know about the underlying tables (sigh!). There are some other problems with the query that I discovered when I restricted the output set and actually got to see some output. It probably should be written as PL/SQL with a cursor loop but it is (or should be) a one-time use thing so I'm spooling the output of this script and re-running that. This should work, though not quickly. SELECT 'SELECT TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLUMNS ' ||CHR(10)|| ' WHERE COLUMN_NAME LIKE '||'''%'||COLUMN_NAME||'%''' || ' AND COLUMN_NAME != '||''''||COLUMN_NAME||''''||CHR(10)|| ' AND TABLE_NAME NOT LIKE '||'''%JN''' ||CHR(10)|| ' MINUS SELECT TABLE_NAME, COLUMN_NAME FROM DBA_CONS_COLUMNS '||CHR(10)|| ' WHERE COLUMN_NAME = '''||COLUMN_NAME||''';' FROM DBA_TAB_COLUMNS WHERE OWNER = 'owner' Dan Tow <dantow @singingsql.com> To: oracle-l@xxxxxxxxxxxxx Sent by: cc: oracle-l-bounce Subject: Re: Slow query 08/16/2004 03:01 PM Please respond to oracle-l 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------