Oracle Database Administration: The Essential Refe

Bitmap indexes

Bitmap indexes can yield greatly improved performance when the data being indexed has low cardinality—that is, if there are relatively few distinct values for the indexed column. An example of a good candidate for a bitmap index would be GENDER, which would have values of “M” or “F”. A poor candidate for a bitmap index would be SALES_AMOUNT, which is likely to have a different value for almost every row.

Creating a bitmap index is similar to creating a standard index; you include the keyword BITMAP in the CREATE INDEX statement. For example, to create a bitmap index on the GENDER column of an EMPLOYEE_MASTER table, you’d specify the following statement:

CREATE BITMAP INDEX empmast_ix ON employee_master(gender);