Re: query not using bitmap index

You need to state the version of Oracle. In the meantime you might like to read an article I wrote for dbazine ( www.dbazine.com ) about bitmap indexes as this may help you to understand the general principles involved.

Note in particular - if a b-tree index is no good for a SINGLE predicate, then a bitmap will (almost always) be just as useless.

The special case you have given can do a bitmap index only scan and count - and
doesn't have to visit the table. Since the index is likely to be much smaller than
the table, this is a smart option for Oracle.

In most versions of 8, the hint should have forced the index into action on the hinted example - is this a test query that you actually ran to prove the point, or simplified extract that you have not actually run ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Kenny Yu wrote in message ...


>I have a table like

>entry(id, type)

>

>The type column is very nondistinct. I created a bitmap index

entry_type_Bmp


>on it.

>A query like

>select /*+ index (entry entry_type_Bmp ) */ * from entry where type

='atype'


>does not use the index and is slow. It get unbearable when I join this

table


>whith type specification in the where-clause and a full scan is performed.

>

>The index entry_type_Bmp  is indeed active since this query

>select count('x')  from entry where type ='atype'

>uses it, as seen from the plan.

>

>Wth a type given in the where-clause, it is apparent that use of the index

>should avoid a full table scan. The hint didn't have effect. what are my

>options?

>

>Kenny

>

>