Re: Optimzer use for an index

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 Fri Apr 20 2001 - 11:29:01 CDT