A bitmap index on the LAST_NAME column of the employee table would
be significantly larger, and many of the benefits of a bitmap index in this case might be outweighed
by the space consumed by the index! Although there are exceptions to every rule, the cardinality
can be up to ten percent of the rows and bitmap indexes will still perform well; in other words, a
table with 1000 rows and 100 distinct values in a particular column will still most likely benefit
from a bitmap index.
NOTE
The Oracle optimizer dynamically converts bitmap index entries
to ROWIDs during query processing. This allows the optimizer to
use bitmap indexes with B-tree indexes on columns that have many
distinct values.
Previous to Oracle 10g, the performance of a bitmap would often deteriorate over time with
frequent DML activity against the table containing the bitmap index. To take advantage of the
improvements to the internal structure of bitmap indexes, you must set the COMPATIBLE
initialization parameter to 10.0.0.0 or greater. Bitmap indexes that performed poorly before
the COMPATIBLE parameter was adjusted should be rebuilt; bitmap indexes that performed
adequately before the COMPATIBLE parameter was changed will perform better after the change.
Any new bitmap indexes created after the COMPATIBLE parameter is adjusted will take advantage
of all improvements.
Using Bitmap Indexes
Bitmap indexes are easy to create; the syntax is identical to that for creating any other index, with
the addition of the BITMAP keyword.
Pages:
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869