Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: optimizer_mode=FIRST_ROWS
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'
cols form dba_ind_columns where index_owner = 'ASDF'
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: oracledba_at_morton-consulting.com
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: ListGuru_at_fatcity.com (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). Received on Tue Mar 18 2003 - 09:04:29 CST