 |
|
Oracle Tips by Burleson |
Bitmapped Index Usage
A bitmapped index is used for
low-cardinality data such as sex, race, hair color, and so on.1 If
a column to be indexed has a selectivity of greater than 30 to 40
percent of the total data, then it is probably a good candidate for
bitmap indexing.
Bitmap indexing is not suggested for
high-cardinality, high-update, or high-delete-type data, as bitmap
indexes may have to be frequently rebuilt in these type
situations.
There are three things to consider when
choosing an index method:
-
Performance
-
Storage
-
Maintainability
The major advantages of using bitmapped
indexes are performance impact for certain queries and their
relatively small storage requirements. Note, however, that bitmapped
indexes are not applicable to every query; and bitmapped indexes,
like B-tree indexes, can impact the performance of insert, update,
and delete statements. Bitmaps store large amounts of data about
various rows in each block of the index structure, and because
bitmap locking is at the block level, any insert, update, or delete
activity may lock an entire range of values.
Bitmapped indexes can provide very
impressive performance improvements. Under test conditions, the
execution times of certain queries improved by several orders of
magnitude. The queries that benefit the most from bitmapped indexes
have the following characteristics:
-
The WHERE clause contains multiple
predicates on low-cardinality columns.
-
The individual predicates on these
low-cardinality columns select a large number of rows.
-
Bitmapped indexes have been created on
some or all of these low-cardinality columns.
-
The tables being queried contain many
rows.
An advantage of bitmapped indexes is that
multiple bitmapped indexes can be used to evaluate the conditions on
a single table. Thus, bitmapped indexes are very useful for complex
ad hoc queries that contain lengthy WHERE clauses involving
low-cardinality data.
Bitmapped indexes incur a small storage cost
and have a significant storage savings over B-tree indexes. A
bitmapped index can require 100 times less space than a B-tree index
for a low-cardinality column.
See Code Depot
www.oracle-script.com |
