When do we create bitmap index?

Question

When do we create bitmap index?

I tried to find out it but I get different answers, so please give me your best opinion.

Answer 1

You should use bitmap indexes on tables with fairly static low cardinality values (a small number of different values which do not change very often). Cardinality should be under 1% for use of a bitmap to be considered and if under 0.1% then it most likely is the best option. There is a high overhead to maintain bitmap indexes compared to b-tree so they are best for data that rarely changes, although the suitability in this case is determined by the application, where perhaps data changes and reporting aren't concurrent and you therefore don't mind the extra effort.

To give a simple example: On an employee table you if you indexed gender, title, department, age and employee# you would choose bitmap for gender and title and possibly for department but probably not for age and definitely not for employee number.