Re: bitmap index not used

How well put.

Add to the list of well-known, frequently promoted, myths.

"The fraction of rows that can be returned efficiently by a bitmap index is much higher than that returned by a b-tree index".

    (Last spotted in Oracle magazine a couple     of issues ago).

As Richard says, an index which is going to make Oracle hit a large number of blocks in the table is probably not going to be used. The fact that the number of leaf blocks Oracle has to visit for a bitmap index is smaller than for the corresponding b-tree is of some benefit, but the number of table-blocks is likely to far outweigh the number of index blocks.

For further reading, there is an excellent article (promoted for several weeks by Tom Kyte, and written by me) on http://www.dbazine.com

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


Richard Foote wrote in message ...


>

>Therefore although the bitmap index is relatively efficient to read

>(compared to it's low cardinality B tree equivalent) it still can be

grossly


>inefficient to use it to access a high proportion of the rows. In this

>example, the FTS is substantially more efficient (in the order of 100 - 200

>times more efficient).

>

>

>Now if you had several bitmap indexes using and conditions to reduce the

>number of accessed rows, then the CBO might show some interest.

>