In this lesson, you will learn how to choose between B-Tree and Bitmap indexing to significantly accelerate data retrieval in your analytical databases.
When you query large tables, the database engine needs a roadmap to find specific rows without scanning millions of records. Two of the most common "roadmaps" are B-Tree indexes and Bitmap indexes. Choosing the right one depends entirely on the cardinality of your data—the number of unique values in a column.
B-Tree Indexing: The High-Cardinality Specialist
A B-Tree (Balanced Tree) index acts like a phone book. It organizes data in a hierarchical, sorted structure. Because it maintains order, it is highly efficient for columns with high cardinality, such as Customer_ID, Transaction_Timestamp, or Email_Address.
If you are performing point lookups (e.g., WHERE Customer_ID = 502) or range scans (e.g., WHERE Transaction_Date BETWEEN '2023-01-01' AND '2023-01-31'), a B-Tree is your best friend.
Bitmap Indexing: The Low-Cardinality Powerhouse
A Bitmap index uses a string of bits (0s and 1s) to represent the presence or absence of a value. This is incredibly space-efficient for low-cardinality columns, such as Gender, Region_ID, or Marital_Status.
Instead of storing every value, the engine creates a bit array for each category. For example, in a Region column:
- North Bitmap:
10010(Rows 1 and 4 are in the North) - South Bitmap:
01101(Rows 2, 3, and 5 are in the South)
Because these bitmaps can be manipulated using fast logical operations (AND, OR, NOT), they are lightning-fast for complex filtering in analytical queries involving multiple low-cardinality attributes. However, they perform poorly on high-cardinality columns because the number of bitmaps required would be massive, making them slow to update and storage-intensive.
Making the Choice
- Use B-Tree: When the column has many unique values and you need to perform range queries or find specific individual records.
- Use Bitmap: When the column has few unique values (categories) and your analytical queries frequently filter or aggregate based on combinations of these categories.
Key Takeaway: Choose B-Tree indexes for high-cardinality data requiring range searches, and reserve Bitmap indexes for low-cardinality categorical data to optimize complex, multi-filter analytical queries.