Bitmap Index :

In previous article i have given the example of indexes with real life example.In this article i would like to explain about the Bitmap Index with real world industry examples. User will get exact idea about the Bitmap indexes with its examples. This article will give you information about using Bitmap Indexes in detail.These indexes are useful in data-warehousing applications.When user needs to handle the huge amount of data user needs to use the indexes to improve the performance.

Bitmap Index Advantages :

In this section i would like to give you some bullet-points of using bitmap indexes.When there are distinct values between 1-100 the bitmap index is very useful.

Point 1 : Reduce the Response time

The Bitmap indexes are used to reduce the response time of most complex queries.

Point 2 : Less Storage

Bitmap indexes will be stored in small storage area as compare to other indexes.

Point 3 : Less Hardware requirements

Bitmap indexes are useful for improving the performance of application when there is less hardware requirements.

Point 4 : Efficient Maintenance

Bitmap index is used for Efficient maintenance.

These are some advantages of using Bitmap indexes. Bitmap index will create two dimensional array for every row in table column which being indexed.The key advantage of using bitmap index is space utilization.The bitmap indexes will take less space than simple index.Regular index will create list of row-ids and bitmap indexes will create bitmap of each key value.Bitmap indexes will provide the most effective performance for the columns in Where clause.

Bitmap indexes are most effective for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.

Bitmap index will not be useful when there are multiple DML operations.

So Be careful when using Bitmap index where there are so many complex DML operations.

Where to Use Bitmap Index ?

In this section i would like to give you where exactly user needs to use the Bitmap indexes.Bitmap indexes will be used when there are multiple distinct values. I will recommend you to use bitmap index when the distinct values are between 1 to 100.Kindly make sure that following important points before using Bitmap indexes.

Point 1 : When Distinct values between 1 – 10 ?

When there are 1 to 10 distinct values the bitmap indexes are very useful.It will improve the performance of query drastically.

Point 2 : When Distinct values between 10 -100?

When there are 10 to 100 distinct values user can use bitmap index. But it will decrease the performance as compare to less distinct values.

Point 3 : When distinct values between 100 -unlimited?

When there are more than 100 distinct values i will recommend you not to use bitmap indexes. It will decrease the performance of queries.

The question in users mind is where to use the Bitmap Indexes in detail,

Kindly check Comparison of Bitmap and B-tree index here

Point 1 : Less than 100 distinct values

When table column contains less than 100 distinct values the bitmap indexes will be useful.

Point 2 : Less DML

When table has less DML operations Bitmap indexes are useful.

Point 3 : Where clause

When in where clause multiple columns used which has less than 100 distinct values use bitmap indexes.

Tips to remember :

Tip 1 : Kindly gather stats after creating the bitmap index.

statement to use gathering stats :

dbms_stats.gather_index_stats(OWNNAME=>’Amit’, INDNAME=>’BM_Name’);

Tip 2 : Kindly check explain plan after creating bitmap indexes on small tables.Sometimes it is not useful for Small tables

Tip 3: Kindly do testing of bitmap indexeswith hint .

select /*+ index(BM_EMP_NAME) */ 
   count(*)
from 
   employee e, department d
where 
   e.deptno = d.deptno;

Some Important Examples of Bitmap Indexes:

In this section I would like to give you some examples of Bitmap indexes in detail.

Example 1 : Bitmap indexes in data-warehouse

The bitmap indexes used in data-warehouse applications.The reporting queries are most complex queries and bitmap indexes are useful in it.

Lets say there is where condition has one column which is grade which has A,B,C,D distinct values.The grade column is in Student table which has lakhs of values.So kindly create bitmap indexes on that column so that you will improve performance of query,

Query :

Select Student_name,Marks from Student where grade = ‘A’;

Create bitmap index BM_Grade on Student(grade);

The above statement will create bitmap index on Student table.

Example 2 : Bitmap indexes used for Is Null condition

The Bitmap indexes can be useful when Is Null condition is there.Make sure that the count of distinct values are less than 100.

Select count(Roll_No) from Student where Roll_No is null;

If this count is less than 100 kindly use bitmap index.

Example 3 : Bitmap indexes on partitioned table

User can create bitmap indexes on partitioned tables but make sure that those indexes are local indexes.Lets say Student table is partitioned table and user needs to create index on Grade column.Then user needs to use local keyword to create index.

Create bitmap index BM_Grade on Student(grade) local;

Example 4 : Bitmap index for Joining operations

User can create Composite bitmap indexes on joining tables.Lets say there are two tables Student and Student_Master which contains Grade and Marks column.The Joining condition will be will Student_id.Then Following index will be useful,

Create bitmap index BM_CI_Student_Master on Student(Student.Grade ,Student.Marks) From Student,Student_Master where Student.Student_id=Student_master.Student_id;

Here user needs to make sure that the index will be created for less than 100 distinct values.

I hope these examples will give you idea about bitmap indexes in detail.If you like this article on Bitmap indexes or if you have any issues and concerns with the same kindly comment in to comments section.