Optimzer use for an index

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!

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site)