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 tested in 188.8.131.52.
Even with RULE based Optimizer, the query execution plan shows the bit map index being used when I added the hint like,
SELECT /*+ INDEX(A TEST_BIT_IDX) */ *
FROM TEST A
WHERE A.STATE = 'NJ' ;
Originally posted by dbbyleo Is that true????!!! When init.ora's OPTIMIZER_MODE=RULE, bitmap indexes are not used, and therefore would require a hint to use the index. Is this what you are saying?
Yes, exactly. With OPTIMIZER_MODE=RULE the only way for the bitmap index to be used is to provide a hint in your query. Without a hint (which in effect activates cost based optimizer instead of RBO), RBO will not even know about your bitmap index.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?