Re: how to avoid fast full scan of index?

In fact let me show you a working example:

Following is cut pasted from sqlplus (oracle version 9.2.0.3):

prod_local table has non-unique index on prd_id product table has a unique index on prd_id Both tables analyzed.

The query is just to show fast full scan disabling ... (I know it can be written better)

SQL> select pl.prd_id from product p, prod_local pl   2 where p.prd_id = pl.prd_id
  3 and pl.prd_id between 10 and 100000   4 /

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=40133 Bytes=401330)

   1 0 NESTED LOOPS (Cost=25 Card=40133 Bytes=401330)    2 1 INDEX (FAST FULL SCAN) OF 'PROD_LOCAL_PK' (UNIQUE) (Cost=25 Card=42971 Bytes=214855)

   3 1 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE) SQL> select /*+ index(pl) */ pl.prd_id from product p, prod_local pl   2 where p.prd_id = pl.prd_id
  3 and pl.prd_id between 10 and 100000   4 /

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=144 Card=40133 Bytes=401330)

   1 0 NESTED LOOPS (Cost=144 Card=40133 Bytes=401330)    2 1 INDEX (RANGE SCAN) OF 'PROD_LOCAL_PK' (UNIQUE) (Cost=144 Card=42971 Bytes=214855)

   3 1 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE) ? Now tell me why after I put in the hint .. even though the cost was higher .. it took the hint and did *not* do a FFS?

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:45:38 CDT