Investigating Design Choices between Bitmap index and B-tree index for a Large Data Warehouse System - Morteza, Zaker; Somnuk, Phon-Amnuaisuk; Su-Cheng, Haw (2008)
Building indexes on database is common, but it has an important impact on the query performance, especially in large databases such as a Data Warehouse where the queries are usually very complex and ad hoc. If a proper index structure is chosen, the query response time call be accelerated. Until now, there is no definite guideline for Data Warehouse analysts to choose the appropriate index. According to conventional wisdom, Bitmap index is a preferred indexing technique for cases where the indexed attributes have few distinct values (i.e., low cardinality). The query response time is expected to degrade as the cardinality of indexed columns increase due to a larger index size. On the other hand, B-tree index is good if the column values are of high cardinality due to its indexing and retrieving mechanisms. In this paper, we show that this may not be true under certain circumstances. Experimental results support the fact that even though the level of column cardinality determines the index file size, but the query processing time is not determined by the level of column cardinality. Moreover, our results indicate that the Bitmap index is faster than B-tree index on a large dataset with multi-billion records.