I have seen bitmap index as the preferred choice in a Data Warehousing environment. I wanted to know reasons for that. As per my own experience, one reason is bitmap indexes are compressed and hence confined in less index space. When we have large number of FK indexes then this index space matters. Bitmap indexes will enable faster search and hence reduced response time in such cases.
I wanted to hear other reasons for this from experts.
As per my own research, bitmap works well for low cardinality columns, however DWH may not necessarily have low cardinality columns.
Bitmap indexes are useful for low cardinality column(s) for 3 primary reasons: 1. Rowid bit map value of indexed values are stored together, making faster access for group data access 2. Bitmap Indexes saves space 3. Bit mapping where 2 or more bitmap indexes can be intersected for faster access. Potential columns for bit map index are gender, country, state, active flag, important indicator columns.
In my opinion, it's better to look at design of the system and access patterns. Building tables with proper partitions is preferred instead of bitmap index. Indexes are overhead so before creating any index, see if you can avoid it. While building DWH , database of choice makes a big difference in designing so system access requirements should be resolved in accordance with physical implementation capabilities of the database.
Thanks Gauri, Can you please elaborate below 2 points with example to avoid any possible ambiguity and gain crystal clarity:
1. Rowid bit map value of indexed values are stored together, making faster access for group data access
3. Bit mapping where 2 or more bitmap indexes can be intersected for faster access.
Regarding point 2, I guess space saving comes due to compression, however compression may be CPU intensive process.
Thanks,
Rajneesh