Re: Why index is not used?

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Sun, 23 Mar 2003 19:14:05 -0000
Message-ID: <3e7e07a9_3@mk-nntp-1.news.uk.worldonline.com>

"Dima" <dima_rogozin_at_yahoo.com> wrote in message news:97a82547.0303221206.4f147730_at_posting.google.com...
> 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.

Not stupid.

CBO can, if it knows that the values are highly skewed. CBO cannot, if all it knows is that there are two possible values.

The following advice is subjective, biased, unproven and YMMV.

Do NOT make a bitmap index.
Use an ordinary index but *gather histograms*. In this case, do *not* use bind variables or stored plans.

Regards,
Paul Received on Sun Mar 23 2003 - 13:14:05 CST