Database Reference
In-Depth Information
The use of bitmap indexes is mainly limited by two situations. First, only B-tree indexes can be used for primary
and unique keys. There's simply no choice here. Second, only B-tree indexes support row-level locking because
locks in (B-tree and bitmap) indexes are internally set for an index entry. Because a single bitmap index entry might
index thousands of rows, a modification of a bitmap-indexed column may prevent the concurrent modification of
thousands of other rows (those referenced by the same index entry), which may greatly inhibit scalability. Another
downside of bitmap indexes is that the database engine generates more redo when they have to be modified. This is
because, in most situations, the keys of a bitmap index are larger than those of a B-tree index.
Note that the selectivity or the number of distinct keys of the index is irrelevant to choose between B-tree and
bitmap indexes. This is true despite the fact that many topics and papers about bitmap indexes contain advice like
the following:
Bitmap indexes are suitable for low cardinality data that is infrequently modified. Data has low
cardinality when the number of distinct values in a column is low in relation to the total number
of rows.
Through compression techniques, these indexes can generate many rowids with minimal I/O.
Bitmap indexes provide especially useful access paths in queries that contain the following:
•
Multiple conditions in the WHERE clause
•
AND and OR operations on low cardinality columns
•
The COUNT function
•
Predicates that select for null values
—Oracle Database SQL Tuning Guide
12c Release 1
Honestly, in my opinion, such information is at the very least misleading. The fact is, a SQL statement with weak
selectivity can never be efficiently executed by getting a list of rowids from an index. That's because the time needed
to build the list of rowids is much smaller, both for B-tree and for bitmap indexes, than the time needed to access
the table with them in such situations—hence, most of the time is going to be spent reading the table, regardless of
the index being a B-tree or a bitmap. That said, it's true that bitmap indexes behave better than B-tree indexes with a
low number of distinct keys (note that in the excerpt, the term
cardinality
has a different meaning than the one used
in this topic!). But be careful,
better
doesn't necessarily mean
efficient
. For example, a
bad
product isn't
good
simply
because it's better than a
very bad
product. It may be that combining bitmap indexes is very efficient, but, once again,
building a list of rowids is just the beginning. The rows still have to be accessed. I should also mention
OR
. If you think
about it, you'll realize that combining several nonselective conditions with
OR
can lead only to even weaker selectivity,
while the goal is to increase it if you want to efficiently use indexes.
■
Tip
Forget about selectivity and cardinality when you have to choose between a B-tree index and a bitmap index.
In addition to the differences summarized in Table
13-2
, B-tree indexes and bitmap indexes don't show the
same efficiency when dealing with the same SQL conditions. Actually, bitmap indexes are usually more powerful.
Table
13-3
summarizes, for both types of indexes, their ability to cope with different types of conditions. The aim of
the following sections is to provide some examples regarding this. I also describe different properties and limitations.
