Databases Reference
In-Depth Information
distinct, valid values). The
GENDER
column in Table 3-1 is an appropriate column for a bitmap index, as
there are only two valid values: male and female.
Table 3-1. Bitmap Index on the GENDER column of the EMPLOYEES Table
Bitmap Index on GENDER Column
Row Number
Employee_ID Employee Name
GENDER/Male
GENDER/Female
Bitmap
Bitmap
1
107
Lorentz,Diana
0
1
2
108
Greenberg,Nancy
0
1
3
109
Faviet,Daniel
1
0
4
110
Chen,John
1
0
5
111
Sciarra,Ismael
1
0
It is also possible to create a bitmap index on the combination of several columns from the
underlying table. The rules still apply, as each column of a composite bitmap index should be a low
cardinality column. Table 3-2 shows a composite bitmap index on the
GENDER
column, which only has
two valid values, and on the
REGION
column, which only has four valid values.
Table 3-2. Composite Bitmap Index on the GENDER and REGION columns of the EMPLOYEES Table
Composite Bitmap Index on Gender and Region Columns
Gender
Male
Gender
Female
Region
East
Region
West
Region
North
Region
South
Row
Employee_ID
Employee Name
1
107
Lorentz,Diana
0
1
1
0
0
0
2
108
Greenberg,Nancy
0
1
0
1
0
0
3
109
Faviet,Daniel
1
0
1
0
0
0
4
110
Chen,John
1
0
0
0
1
0
5
111
Sciarra,Ismael
1
0
0
1
0
0
When choosing whether or not a column is appropriate for a bitmap index, the basic rule that it
should be a low cardinality column needs to be put into perspective. Based on your application, the
makeup of your data, and the tables in your database, what is appropriate for a bitmap index may vary
greatly. One basic rule of thumb that can be used is that if the valid values for a column make up less
