Bitmap Index
Learn via video course
FreeOverview
Database Management System uses Bitmap Indexing for large tables to fetch rows from columns having low cardinality. The term cardinality is the count of distinct elements in a column. If a column has fewer distinct elements, then the cardinality of that column is low. A bit in the bitmap represents the smallest unit of data, a value of 0 and 1 is represented by the bitmap and mapped with the column values. This allows bitmap indexing to retrieve results faster by using bitwise logical operations on the columns. This technique is used in large databases and on tables with low cardinality to optimize performance.
Scope
- Using bitmaps on your columns having low cardinality will allow you to achieve faster retrieval of data.
- In this article, we explore the need for Bitmap indexing in DBMS, as well as its structure and syntax.
- An example-based discussion of Bitmap Indexing is provided in this article.
Introduction to Bitmap Indexing
An indexing technique known as Bitmap Indexing enables data to be retrieved quickly from columns that are frequently used and have low cardinality. Cardinality is the count of distinct elements in a column.
If a column has fewer distinct elements, then the cardinality of that column is low. Bitmap allows you to reduce the response time of the bigger SQL queries. As an example of a column with low cardinality, consider the "Gender" column, which has two values, Male and Female, which can be equivalent to bit values, thus 0 and 1. In addition, the values assigned to the columns are logically computed to get the results.
Note : To use the bitmap index, the columns need to be arranged sequentially.
Need of Bitmap Indexing
Student Table :
Let's take a look at a student table that has the columns Roll_no, Name, Gender, and Result.
| Roll_no | Name | Gender | Result |
|---|---|---|---|
| 01 | Geeta Raj | F | Fail |
| 02 | Deep Singh | M | Fail |
| 03 | Ria Sharma | F | Pass |
| 04 | Ajit Singh | M | Fail |
| 05 | Jitu Bagga | M | Pass |
| 06 | Neha Kapoor | F | Pass |
Here's an example to help you understand why we need bitmap indexing in DBMS. Think about a College database with a table named Student where we store student information like name, gender, and their results.
We use this information for everything else we do, such as counting the number of male students who passed or failed. If this table contains so much data then it will take time to fetch rows from it. Therefore, to retrieve the desired data in the least time possible, we use Bitmap Index by bit mapping the columns with low cardinalities like gender and results. As the "gender" and "results" columns contain only two distinct elements, the elements in those columns are mapped with bits, which means that each element is either 0 or 1.
Let's understand the structure of Bitmap Index and how Bitmap Indexing is done.
Bitmap Index Structure
In general, Bitmap combines the terms Bit and Map, where bit represents the smallest amount of data on a computer, which can only hold either 0 or 1 and map means transforming and organizing the data according to what value should be assigned to 0 and 1.
How Bitmap Indexing is Done?
Let's look at a table called Student and see how Bitmap is applied to it.
Fig. Bitmap logic on table
Here, we have a Student table where we have 2 columns with low cardinality. Let's consider a problem statement, Query all the students who are female and have passed the exam.
For the query above, the And operation is performed between Gender = F and Result = Pass. All the Gender = F values are mapped as 1(which means true) and M as 0(which means false) whereas, in the Result column same happens with Pass and Fail. Therefore, bimap is the same in its operation, except that logically it operates on bits, making it faster to return the same results. Instead of fetching each row, a logical "And" operation is performed, and the 1(in output under Names shown below) indicates the names that are to be retrieved as output.
As an output represents the Name column that has to be retrieved as a result.
Output :
| NAME |
|---|
| Ria Sharma |
| Neha Kapoor |
Bitmap Indexing in SQL
This syntax can only be used by Oracle because MySQL is unable to support bitmap indexing.
A Bitmap Index is created using the following Syntax for a particular column of a particular table :
Syntax :
The _Index_name_ represents the name that you give to an index along with that to apply the bitmap to a particular column, you must specify the Table name and the exact column name.
Therefore, in the above example, the Bitmap on the Student table will look like the following :
Query :
Output :
The Student_bitmap in the above query indicates the bitmap that is applied on table name = Student and column names = Gender and Results.
Let's have a look at the advantages and disadvantages of bitmap indexing.
Advantages :
- Compared to the traditional method, records can be retrieved faster as it fetches the rows in form of bits.
- You can combine several bitmap indices to get the desired results.
- In addition to being efficient, this method is more effective when the columns have the least number of inserts, updates, and deletions.
Disadvantages :
- On tables with few records, it is not recommended to use bitmap as the response time is already less for them.
- A new record has to be entered into the table every time, which can be time-consuming and difficult to edit. As a result bitmap indexing is more efficient with static tables.
- In the case of record deletion, the sequence of records is disturbed and creates gaps between other records.
Conclusion
We can come to an undisputed conclusion that,
- In DBMS bitmap Indexing enables data to be retrieved quickly from columns that are frequently used and have low cardinality.
- Bitmap combines the terms Bit and Map, where bit represents the smallest amount of data on a computer, which can only hold either 0 or 1 and map means transforming and organizing the data according to what value should be assigned to 0 and 1.
- By bit mapping the column values in binary form and using bitwise logical operations (like AND) on them, bitmap indexes can represent the column values.
- This syntax can only be used by Oracle because MySQL is unable to support bitmap indexing.
- The whole idea of a bitmap index is to reduce query execution time when dealing with larger static tables.