Re: Slow query

  • From: Thomas Day <tday6@xxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 16 Aug 2004 15:27:37 -0400

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
-----------------------------------------------------------------

Other related posts: