Most of the people think that if you have a column in a table which has low cardinality then we should have BITMAP index. But that is not always true. Consider this case.
You have an employee table when you have a column called Active_flag which has 3 distinct values
R=> Retired, and a employee_id column which is also indexed.
You may think that if you have a bitmap index on the active flag and if you include that in where clause then your query will be faster. Its true if you have ONLY active_flag in your where clause. If you have employee_id in the where clause then definitly oracle will consider using the bitmap index on the Active_flag.
The consider the below scenario.
You are given a basket in which one is fully filled with 1000000 “yellow” color cubes which is numbered from 1 to 1000 and 1000000 “red” color cubes numbered 1 to 1000. If
If ask you to pick asking you to pick “yellow” cube with number 500 you would definitly make use if the colour (which is the bitmap index)
You are given a basket in which one is fully filled with 1000000 “yellow” color cubes which is numbered from 1 to 1000000 and 10 “red” color cubes numbered 1 to 10.
If ask you to pick asking you to pick yellow cube with number 500 would you use the colour??
The colour is the bitmap index and the number is binary index. Also BITMAP index eat more memory. So people use the bitmap index sensibly.