Databases Reference
In-Depth Information
If you decide to implement bitmap indexes within your application, it's a good idea to occasionally
check the makeup of the data within the columns that are bitmap indexed. This is especially important if
you have bitmap index maintenance operations as part of your overall application. If you miscalculate
the cardinality of any columns with bitmap indexes, it could cause negative ramifications for your
application, such as:
•
Storage for the bitmap indexes will increase.
•
Query performance will degrade.
Rebuild operation times will increase.
It is especially important to review the makeup of the data with new applications where the makeup
of the data isn't known initially. Sometimes you have to make assumptions during application design,
which you should reassess after you start seeing "real" data. After the initial implementation of your
application, take time to reassess cardinality on columns at least once to validate any assumptions you
relied upon for bitmap indexes. You can then take steps to modify your application appropriately, which
may include converting some bitmap indexes to B-tree indexes or vice versa. Modifications also may
include restructure or removal index maintenance operations if you need to remove bitmap indexes or
add some index maintenance operations if you need to add bitmap indexes.
•
Creating a Bitmap Index
Once you have determined that bitmap indexes are appropriate for your environment, it is fairly
straightforward to create a bitmap index. The syntax is the same as it is for a B-tree index, except that
you need to include the keyword
BITMAP
when creating the index. For example,
CREATE BITMAP INDEX EMPLOYEES_B1
ON EMPLOYEES (GENDER)
NOLOGGING;
Because the physical structure of a bitmap index is so simple, and therefore much smaller than a B-
tree index, you can create them much faster than a B-tree index. Note that the
NOLOGGING
keyword is used
in the previous example. We recommend you always create bitmap indexes with
NOLOGGING
because
DML operations on bitmap indexes perform so poorly that it's a good idea to destroy and rebuild them
rather than maintain them. Of course, as with any database objects with NOLOGGING specified, you will
need to rebuild any bitmap indexes if any database recovery is required. In the following example, you
create an index on the
GENDER
column of the
EMPLOYEES
table. Note the time it took to create the bitmap
index.
SQL> CREATE BITMAP INDEX EMPLOYEES_B2
2 ON EMPLOYEES (GENDER)
3* NOLOGGING;
Index created.
Elapsed: 00:00:10.01
