The version is 8.1.6 and 8.1.7.
The reason why the first query has dba_tables in it at all is that without it,
columns that are no longer there keep showing up. In other words, there are
columns in dba_tab_columns with no corresponding table row in dba_tables. The
link to dba_tables was to weed out these left-overs. I think I remember a
post here a while back where someone said that happens occassionally but I
can't remember why.
Regarding your suggestion to do the in-line views, I had tried that but
without the no_merge hint. Without it, I had the same "hang" problem. But
after your suggestion, I added the no_merge hint and it runs fine (and in less
time). Excellent....
Thanks!
Karen
-----------------------------------------
Which version of Oracle is this ?
The views have changed over time,
and I can't spot why your first query
needs a join to dba_tables - all the
relevant columns seem to exist in
dba_tab_columns in my version of 8.1
As a general rule, when there is no
obvious quick and clean way of joining
catalog views, one option is to use
the no_merge hint and in-line views.
This isn't necessarily the fastest or
cleverest solution, but it can be the
most obvious to implement.
select {list of columns}
from
(select /*+ no_merge */
cols from dba_indexes
where index_owner = 'ASDF'
) ix,
(select /*+ no_merge */
cols form dba_ind_columns
where index_owner = 'ASDF'
) ic
where
ic.index_owner = ix.owner
and ic.index_name = ix.index_name
and ....
This may result in two fairly efficient
queries running separately, followed
by a hash join (or perhaps merge join)
between the two sets of generated data.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Karen Morton
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
