From: "Tanel Poder" <tanel@@peldik.com>
Newsgroups: comp.databases.oracle.server
References: <894b11eb.0308080802.65850337@posting.google.com> <0TPYa.268$aE1.186@nwrddc01.gnilink.net>
Subject: Re: how to avoid fast full scan of index?
Date: Fri, 8 Aug 2003 20:21:03 +0300
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 213.35.164.40
Message-ID: <3f33dc13$1_1@news.estpak.ee>
X-Trace: news.estpak.ee 1060363283 213.35.164.40 (8 Aug 2003 20:21:23 +0300)
X-Complaints-To: usenet@estpak.ee
Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.astraweb.com!news-small.astraweb.com!newsfeed.news2me.com!newsfeed.icl.net!newsfeed.fjserv.net!proxad.net!proxad.net!news-stoc.telia.net!news-stoa.telia.net!telia.net!nntp.inet.fi!inet.fi!newsfeed2.funet.fi!newsfeeds.funet.fi!newsfeed.uninet.ee!news.ut.ee!news.estpak.ee!not-for-mail
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240117

> select /*+   index(g)  */ .....
>
> IMHO: it will avoid the fast full scan. Since it will override the
> index_ffs(g) clause.

No, it will just tell CBO to use an index access path (fast full scan or
skip scan for example).
There is an event: "10120 CBO Disable index fast full scan", but it didn't
work in my test env (9.2.0.1 W2k).

Before I start speculating about optimizer_* parameters, it would be helpful
to have both execution plans and your db version information. Also make
sure, that your second join SQL is correct, not missing any predicates etc..

Tanel.

>
> Try that.
>
> Anurag
>
>
> "oofoof" <oofoofoof@ureach.com> wrote in message
> news:894b11eb.0308080802.65850337@posting.google.com...
> > I have a large query generated by a tool that joins tables
> > A-B-C-D-E-F-G-H-I
> > I modified the query to produce the same results by joining tables:
> > A-F-G-H-I
> >
> > A composite index is used by Oracle on table G in both queries, but in
> > the first case, Oracle uses an index range scan on that index while in
the
> > second case, Oracle uses a fast full scan on the same index. So, instead
> of
> > the second query running faster (joins fewer tables), it runs slower.
> > The query selects only a small subset of entries in the index and the
> index
> > has the 2 columns required to satisfy the query (no table access is
> necessary).
> > What can I do to avoid the fast full scan? I could not find a hint that
> > accomplished it. Will building a histogram on the index help?
>
>