Oracle NOT using BITMAP index

[dbbyleo]

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.

[prakashs43]

some of the restrictions of Bitmapped indexes

- Bitmapped indexes are not supported for trusted oracle

- Not used by teh rule based optimiser

- Cannot be used on a partitioned table as a global index

[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?

[tamilselvan]

I tested in 8.1.7.4.
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' ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=105 Card=5002 Bytes=85
034)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=105 Card=50
02 Bytes=85034)

2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'TEST_BIT_IDX'
Post your init.ora file here.


[Edited by tamilselvan on 09-11-2002 at 05:21 PM]

[jmodic]

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.

[dbbyleo]

Thanks you guys for confirming that.

So sorry for such a late reply from me. For some reason, this forum is not notifying me by email when a post is made to my threads.

Anyhow, thanks.