Re: how to avoid fast full scan of index?

I actually suggested that because it has worked for me many times. In fact I do not
recollect when I was not able to convert the fast full scan to range scan this way.
So I'm not sure if index(g) means any kind of index scan .. or just plain range/unique index scan.
Now I admit that I might be wrong .. however, its worth a test at least?

How are you so sure about your comment? Is that documented anywhere?

Anurag

"Tanel Poder" <tanel@@peldik.com> wrote in message news:3f33dc13$1_1_at_news.estpak.ee...
> > 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_at_ureach.com> wrote in message
> > news:894b11eb.0308080802.65850337_at_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?
> >
> >
>
>
Received on Fri Aug 08 2003 - 20:11:40 CDT