Databases Reference
In-Depth Information
When we first execute this set of queries, the execution plan is always a
FULL
TABLE
SCAN
operation.
We have hence created a
BITMAP
INDEX
on the table, choosing three fields as our key
fields on which we are filtering the queries.
After index creation, we execute (once again) the same set of six queries. This time the
execution plan uses the newly created bitmap index, with great enhancement in the
number of database blocks processed, using the
INDEX
RANGE
SCAN
or
INDEX
FAST
FULL
SCAN
operation, depending on whether we are filtering on the first key column of
the index—
CUST_GENDER
—or not.
This result is obtained thanks to the particular structure of bitmap indexes. In this kind
of index, the database stores few rows. These rows contain the different values in the key
fields and a bitmap—which is a map of binary values—in which for every row there is a bit,
which is "on" for value 1. For that row, the index key values correspond to those of the
bitmap index row to which the bitmap belongs.
There's more...
Bitmap indexes offer very fast performance when we have a low cardinality field indexed on
a table containing many rows. There isn't a fixed rule to define when field cardinality is "low":
the
CUST_GENDER
column surely is, similar to a
COUNTRY_ID
ield.
BILL_ID
will likely have
very large cardinality, but we have to look at the table cardinality too. On a table containing
100 records, or 100 million records, a notion of low cardinality isn't the same.
When is it better to not use bitmap indexes? In OLTP environments.
When rows are frequently inserted, deleted, and updated, there is a performance bottleneck
if we use a bitmap index. When the index is updated, all the bitmap segments are locked.
Indeed, the database cannot lock a single entry in the bitmap. So, when there are concurrent
updates it will lead to poor performance.
The ideal environment where bitmap indexes fit very well is with Decision Support Systems,
where data in tables is quite static by nature, read-only, with many rows in the table.
Another difference between bitmap and B-tree indexes is in how
NULL
values are managed.
In bitmap indexes they are stored, in B-tree they are not.
There is also a big advantage in space when using bitmap indexes against B-Tree. Typically, a
bitmap index uses between 2 percent and 10 percent the space of the corresponding B-Tree
index on the same key fields. So even
INDEX
FAST
FULL
SCAN
operations are faster with
bitmap indexes (we have seen that B-Tree indexes are capable of
FAST
FULL
SCAN
of the
values for key columns other than the first).
