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: optimizer_mode=FIRST_ROWS

Re: optimizer_mode=FIRST_ROWS

From: Karen Morton <oracledba_at_morton-consulting.com>
Date: Tue, 18 Mar 2003 07:04:29 -0800
Message-ID: <F001.0056C799.20030318070429@fatcity.com>


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

Original text of this message

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