Re: Optimzer use for an index

The optimizer may also be favouring FTScans or IFFScans if DB_FILE_MULTIBLOCK_READ_COUNT is set too high.

D.P.

Sybrand Bakker wrote:
>
> On Fri, 20 Apr 2001 11:34:42 -0400, "Syltrem" <syltrem_at_videotron.ca>
> wrote:
>
> >Hi!
> >
> >I have a table with an index on 3 fields A, B and C
> >
> >I have a SELECT ... WHERE A=1 and B=2 and Z=0);
> >
> >I would expect Oracle to use the index, regarding only for matches on fields
> >A and B (and then checking if Z=0 among those) but it does not.
> >
> >Now if I create a second index with only fields A and B, then Oracle does
> >use the new index and the query runs in 2 seconds instead of 55.
> >
> >WHY does Oracle not use the first index? I consider it is suitable to
> >achieve the same result we get with a second index on fields A and B only
> >(in other languages, it is). I don't get it. And I don't see why I should
> >create 2 indexes on the same fields (more overhead on row creation).
> >
> >Thanks!
>
> Please post more background
> - the optimizer you are using
> - the *full* index definition (it is important to know whether it's
> unique or not)
> - execution plans.
>
> If you are using CBO the answer would probably be:
> - right now, according to CBO it would take less I/O to perform a full
> table scan
> - as the second index is smaller the situation is probably reversed
> - the first index is not unique
> and finally
>
> - try creating histograms on affected columns
>
> BTW : if you have three columns on an index and all three columns in
> the where clause, Oracle will always use *all* three columns. The
> scenario you describe above will never apply.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
Received on Sat Apr 21 2001 - 21:40:12 CDT