Re: Why index is not used?

On 22 Mar 2003 12:06:32 -0800, dima_rogozin_at_yahoo.com (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.

Hi Dima,
Analyze the table + the index in question and problem is gone ( with 99% certainty ). If not, check if the index is valid. If it is valid, rebuild the index, analyze and try again. If index is still not used, return to this newsgroup, because then we have a case.

• Kenneth Koenraadt