Bitmap indexes

· What?
· Leaf Entry Format
· B-Tree Index vs Bitmap index
· B-Tree space vs Bitmap space
· The choice between bitmap index and Btree index
· References

What?

• For indexing columns with a low set of unique values, bitmap indexing can be used → For Low cardinality columns
• Stores a bitmap for each key value. Each bitmap represents a unique value
• A bitmap indicates the presence or absence of a value in a dataset, using 1’s and 0’s.
  → For existing values, the position of the 1 in the bitmap shows the location of the row in the table.
• This allows for efficient filtering and querying of the data based on the values in the column. Bitmap indexes are particularly useful for read-intensive workloads, as they can provide fast query performance for equality and range queries.
• Bitmap indexes are very effective in handling complex queries where multiple columns are used.

Source — https://mjromeo81.com/2017/01/28/introduction-to-bitmap-indexes/

Leaf Entry Format

• Header : chaining information, row lock status, number of columns
• Key column length and value pairs
• Start ROWID, End ROWID : the first row and the last row pointed by the bitmap (block num, rownum, file num)
• Bitmap : a string of bits depending on key value

B-Tree Index vs Bitmap index

B-Tree space vs Bitmap space

The choice between bitmap index and Btree index

The choice between bitmap index and Btree index depends on the specific characteristics of the data and the types of queries that need to be optimized.

• If the column has low cardinality (relatively small number of distinct values) and the queries involve equality or range operations, a bitmap index can be a good choice. Bitmap indexes have a compact representation, which can save storage space compared to Btree indexes. They are efficient for read-intensive workloads and can provide fast query performance.
• On the other hand, if the column has high cardinality (large number of distinct values) and the queries involve various operations, including equality and range queries, a Btree index can be more suitable. Btree indexes provide efficient performance for a wide range of queries and are well-suited for high-cardinality columns.

References

Introduction to Bitmap Indexes

LUC #14: Optimize Your Searches with Effective Database Indexing

Data Warehousing Seminar Chapter 13 Indexing the Warehouse - ppt video online download