Database Related

What Is A Bitmap Index?

So, what is a bitmap index?

A bitmap index is a special way of arranging information in a relational database to save time and resources. They have specific use cases so it is not something we will run into constantly. Let me explain why.

Bitmap indexes organize information for only one column of data in a table.  So, if we have a table in our database that describes students, the bitmap index would only organize the column that describes the student’s gender, or maybe the student’s enrollment status. Bitmap indexes do not index the entire table or every column in the table.

Next, bitmap indexes are only feasible to use for a column that has very few possible values like the student’s gender or enrollment status. A student can only be a male or female, or can only be enrolled or not enrolled. There can’t be more options. Bitmap indexes really shouldn’t handle data that has more than one or two options because of the way they are structured.

Example:

Here is our student table

Primary key Name Gender Enrolled Street Address
1 Billy Bob Male Yes 123 Fake St.
2 Jane Female No 456 Nowhre Ln.
3 Sue Female No 356 Righthre Ave.
4 Mark Male Yes 289 Oops Terrace
5 Timmy Male Yes 129 Cat Blvd.

 

The bitmap index pulls out the gender column and creates an index for it:

Gender Row1 Row2 Row3 Row4 Row5
Male 1 0 0 1 1
Female 0 1 1 0 0

 

That may seem like extra work, but a computer can crunch through that bitmap index much faster than it can a database table.

Like anything in computer science though, there are tradeoffs. A bitmap index will always be the same number of bits in size as there are rows in the database. If the database has 10,000 rows, the bitmap index will be at least 10,000 bits. The example above uses a pretty binary value, either true or false. This is one of the reasons that it doesn’t make sense to use a bitmap index for a column that can have a lot of values, like the street addresses of students. The bitmap index would be very large and, because each value is unique, really wouldn’t save any resources.

The last point to make is that bitmap indexes work best on static data. It takes a lot to move that index around and rearrange it, and the index does need to be rearranged each time a row is changed in the database.

 

 

Leave a Reply