Bitmap indexes in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a bitmap index?

A bitmap index is a type of database index that uses bit arrays (bitmaps) to quickly answer queries, especially useful for columns with a small number of distinct values.

beginner

How does a bitmap index represent data internally?

It uses a series of bits where each bit corresponds to a row in the table. A bit is set to 1 if the row has the value represented by that bitmap, otherwise 0.

intermediate

Why are bitmap indexes efficient for columns with low cardinality?

Because they use compact bit arrays, bitmap indexes require less space and can quickly combine bitmaps using bitwise operations for queries on columns with few distinct values.

intermediate

What types of queries benefit most from bitmap indexes?

Queries that filter on multiple low-cardinality columns, such as AND, OR, and NOT conditions, benefit because bitmaps can be combined quickly with bitwise operations.

intermediate

What is a limitation of bitmap indexes?

Bitmap indexes are not efficient for columns with high cardinality or for tables with frequent updates, because bitmaps can become large and costly to maintain.

What kind of data is best suited for bitmap indexes?

A Columns with few distinct values

B Columns with many unique values

C Large text columns

D Columns with continuous numeric values

Bitmap indexes work best on columns with low cardinality, meaning few distinct values.

How does a bitmap index speed up query processing?

A By compressing text data

B By sorting data physically

C By caching entire tables

D By using bitwise operations on bit arrays

Bitmap indexes speed up queries by combining bitmaps with fast bitwise operations like AND, OR, and NOT.

Which operation is NOT typically efficient with bitmap indexes?

A AND

B Frequent row insertions

C OR

D NOT

Frequent updates or insertions are inefficient for bitmap indexes because bitmaps need to be updated, which can be costly.

What does each bit in a bitmap index represent?

A A row in the table

B A column in the table

C A database user

D A query condition

Each bit corresponds to a specific row, indicating whether that row has the indexed value.

Why might bitmap indexes use less storage space than other indexes?

A Because they avoid indexing

B Because they store full row data

C Because bits are compact and can be compressed

D Because they use text compression

Bitmaps use bits which are very compact and can be further compressed, saving storage space.

Explain how bitmap indexes work and why they are useful for low-cardinality columns.

Think about how bits can quickly show which rows match a value.

Describe the main advantages and limitations of bitmap indexes in databases.

Consider when bitmap indexes shine and when they struggle.

Practice

1. What is the main advantage of using bitmap indexes in a database?

easy

A. They improve write performance for frequent updates

B. They reduce the size of the database tables

C. They automatically backup the database

D. They speed up queries on columns with few unique values

Solution

1. Step 1: Understand the purpose of bitmap indexes

   Bitmap indexes are designed to speed up searches on columns that have a small number of distinct values, like gender or status.

2. Step 2: Compare options with this purpose

   Only They speed up queries on columns with few unique values correctly states that bitmap indexes speed up queries on such columns. Other options describe unrelated features.

3. Final Answer:

   They speed up queries on columns with few unique values -> Option D

4. Quick Check:

   Bitmap indexes = speed up queries on low-cardinality columns [OK]

Hint: Bitmap indexes help when column values repeat often [OK]

Common Mistakes:

• Thinking bitmap indexes improve write speed
• Confusing bitmap indexes with data compression
• Assuming bitmap indexes backup data automatically

2. Which of the following is the correct way to describe a bitmap index?

easy

A. An index that stores row pointers in a tree structure

B. An index that stores full copies of data rows

C. An index that uses bits to represent the presence of values

D. An index that hashes values for quick lookup

Solution

1. Step 1: Recall bitmap index structure

   Bitmap indexes use bits (0 or 1) to indicate whether a row contains a specific value in a column.

2. Step 2: Match description to options

   An index that uses bits to represent the presence of values correctly describes this bit-based representation. Other options describe different index types.

3. Final Answer:

   An index that uses bits to represent the presence of values -> Option C

4. Quick Check:

   Bitmap index = bit representation of data presence [OK]

Hint: Bitmap means bits show if value exists in row [OK]

Common Mistakes:

• Confusing bitmap indexes with B-tree indexes
• Thinking bitmap indexes store full data rows
• Assuming bitmap indexes use hashing

3. Consider a column Gender with values 'M' or 'F' in a table of 1000 rows. How does a bitmap index represent this data?

medium

A. Two bitmaps, one for 'M' and one for 'F', each with 1000 bits

B. One bitmap with 2000 bits combining both values

C. A bitmap with 1000 bits storing the actual characters 'M' or 'F'

D. A bitmap that stores row numbers where values appear

Solution

1. Step 1: Understand bitmap index for low-cardinality columns

   For each distinct value, a bitmap index creates a bitmap with one bit per row indicating presence (1) or absence (0).

2. Step 2: Apply to 'Gender' column

   Since 'Gender' has two values ('M' and 'F'), there will be two bitmaps, each 1000 bits long, one for 'M' and one for 'F'.

3. Final Answer:

   Two bitmaps, one for 'M' and one for 'F', each with 1000 bits -> Option A

4. Quick Check:

   Bitmap per distinct value = two bitmaps of 1000 bits [OK]

Hint: One bitmap per distinct value, bits equal to rows [OK]

Common Mistakes:

• Thinking bitmap stores actual characters
• Assuming one combined bitmap for all values
• Confusing bitmap with row number storage

4. A bitmap index is used on a column that is frequently updated. What is the likely problem?

medium

A. Bitmap indexes improve update speed but slow down queries

B. Bitmap indexes slow down updates and cause locking issues

C. Bitmap indexes automatically adjust to frequent updates without issues

D. Bitmap indexes convert updates into batch inserts

Solution

1. Step 1: Recall bitmap index behavior on updates

   Bitmap indexes are not ideal for columns with frequent updates because changing bits can cause locking and slow performance.

2. Step 2: Evaluate options

   Bitmap indexes slow down updates and cause locking issues correctly states that bitmap indexes slow down updates and cause locking. Other options are incorrect or misleading.

3. Final Answer:

   Bitmap indexes slow down updates and cause locking issues -> Option B

4. Quick Check:

   Frequent updates + bitmap index = slow and locking [OK]

Hint: Bitmap indexes bad for frequent updates [OK]

Common Mistakes:

• Assuming bitmap indexes speed up updates
• Thinking bitmap indexes handle updates automatically
• Believing bitmap indexes convert updates to inserts

5. In a data warehouse, a bitmap index is created on a Region column with 5 distinct values. Which scenario best explains why bitmap indexes are preferred here?

hard

A. The column has few unique values and queries often filter by region

B. The column is updated every minute with new region codes

C. The column stores large text descriptions of regions

D. The column has millions of unique region codes

Solution

1. Step 1: Identify characteristics suitable for bitmap indexes

   Bitmap indexes work best on columns with few unique values and mostly read-only data, like in data warehouses.

2. Step 2: Analyze each option

   The column has few unique values and queries often filter by region fits perfectly: few unique values and frequent filtering. Options B, C, and D describe unsuitable scenarios.

3. Final Answer:

   The column has few unique values and queries often filter by region -> Option A

4. Quick Check:

   Few unique values + read-heavy queries = bitmap index ideal [OK]

Hint: Bitmap indexes suit low-cardinality, read-heavy columns [OK]

Common Mistakes:

• Using bitmap indexes on frequently updated columns
• Applying bitmap indexes to high-cardinality columns
• Confusing bitmap indexes with full-text indexes