Why index is not used?

I guess this subject has been bitten to death but here we go again:

I have a table which has an indexed column status. The value can be 1 and 2 (it can actualy have other values so it can not be a boolean but it is not importante for the problem below). The table is very big. Most of the time _all_ the rows have status equal 1. Sometimes a few (up to 10) rows will have status 2.

If I execute a query "select * from my_table where status = 2" it does full table scan in both cases (no records WITH status=2 and a few records).

Now the stupid question: why can't Oracle look at the index and say: "Ok, I don't have any values for 2 in my index -> return no rows". For the second case, it looks like I almost need an "index of an index" kind of thing.

Any advise is greatly apresiated,
D. Received on Sat Mar 22 2003 - 14:06:32 CST