I recently created a bitmap index on a table, with a 19 million rows, for a column that had only 120 distinct values (low cardinality). However, when I query the table (using the column in the where clause), the explain plan still returns a full table scan.

I've analyzed the table (using compute), although that shouldn't matter since we are in Rule Based Optimizer mode. I end up having to use a hint to get Oracle to use the index.

Can someone explain why this is happening?

I've flushed the shared pool, thinking that it may be still using the previously parsed execution plan, but that still doesn't get it to use the index.

I'm using 8.1.6, BTW.