Re: Why index is not used?

Dima wrote:

> 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.

What version and edition?
How many rows is "The table is very big"? Which optimizer? RBO or CBO?
Have current statistics been collected using DBMS_STATS? Have you run EXPLAIN PLAN?
Have you tried using a HINT?
Are you sure an index exists? What type? B*Tree or Bitmap?

Daniel Morgan Received on Sat Mar 22 2003 - 15:05:35 CST