Re: Why index is not used?

B-tree indexes on data with a cardinality of two are usually a complete waste of time, since the selectivity is ridiculously low (pick a value to select by, and you end up searching through 50% of the data... so Oracle quite rightly assumes that it would be quicker to do a full table scan [which is optimised with multi-block reads] than to visit half the index AND most of the table anyway). Bear in mind that reads against an index are always done leaf node by leaf node. A full index scan, in other words, is only ever done with many individual I/O operations, making them relatively expensive.

In general, Oracle's multi-block read optimisation for full table scans means that your query needs to select for around 2 to 5% of the table rows before the use of an index is deemed appropriate by the optimiser (though other factors come into play, such as the clustering factor of the index -ie, does an index leaf node point to just one table block, or several).

Your situation is slightly different, however, in that you have (it would seem) a wild data skew, so that 90%+ of your values are "1" and very, very few are "2". The main factor here would therefore be whether Oracle is *aware* of the skew.. and it can only be so aware if you give it a helping hand by calculating a histogram on the relevant table column (ie, 'analyze table blah compute statistics for columns STATUS').

You might also find that compressing the index would be advantageous ('create index blah on table(status) compress'), assuming you have 8.1.6 or higher. With many repeated "1" values, this should result in a much smaller index, and smaller indexes, since they can be read more economically than big ones, tend to get used more frequently by the optimiser without the need to hint your SQL statements.

But for low cardinality columns in general, you should usually consider bitmap indexes, although these have a rather nasty side-effect of locking many hundreds of table rows when just one row in the table has its key column updated... so no bitmap indexes anywhere near DML, in other words.

Regards
HJR "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.
Received on Sat Mar 22 2003 - 14:49:28 CST