Bitmap indexes in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Bitmap Index Structure

What best describes the structure of a bitmap index in a database?

A A bitmap index is a hash table mapping each distinct value to a list of row pointers.

B A bitmap index uses a bitmap (array of bits) for each distinct value in a column, where each bit represents a row indicating presence or absence of that value.

C A bitmap index stores sorted lists of row IDs for each distinct value without using bits.

D A bitmap index stores the actual data values in a compressed tree structure for faster retrieval.

💡 Hint

Think about how bits can represent presence or absence efficiently.

A bitmap index creates a bitmap for each distinct value in a column. Each bit in the bitmap corresponds to a row in the table. If the bit is 1, the row contains that value; if 0, it does not. This allows very fast logical operations for queries.

📋 Factual

intermediate

Best Use Case for Bitmap Indexes

In which scenario is a bitmap index most effective?

A When the column has low cardinality, such as gender or boolean flags.

B When the column has a high number of distinct values, like unique IDs.

C When the table is frequently updated with many insertions and deletions.

D When the column contains large text strings requiring full-text search.

💡 Hint

Consider how many distinct values the column has and how that affects bitmap size.

Bitmap indexes work best on columns with low cardinality (few distinct values) because the bitmaps remain small and efficient. High cardinality columns would create large bitmaps, reducing efficiency.

🔍 Analysis

advanced

Query Performance with Bitmap Indexes

Consider a query filtering rows where two low-cardinality columns meet certain conditions combined with AND. How does a bitmap index improve performance?

A It duplicates the data in memory to avoid disk access.

B It sorts the rows physically on disk to speed up sequential reads.

C It creates a temporary hash table for the query to speed up lookups.

D It allows combining bitmaps with bitwise AND operations to quickly find matching rows without scanning the table.

💡 Hint

Think about how bitmaps can be combined logically.

Bitmap indexes allow fast bitwise operations like AND, OR, and NOT on bitmaps representing different conditions. This lets the database quickly find rows matching multiple conditions without scanning all rows.

❓ Comparison

advanced

Bitmap Index vs B-Tree Index

Which statement correctly compares bitmap indexes and B-tree indexes?

A Bitmap indexes require more storage space than B-tree indexes for the same data.

B Bitmap indexes are better for columns with many distinct values, while B-tree indexes are better for low-cardinality columns.

C B-tree indexes support fast range queries, while bitmap indexes are less efficient for range scans.

D B-tree indexes cannot be used for equality searches, unlike bitmap indexes.

💡 Hint

Consider how each index type handles range queries and distinct values.

B-tree indexes are efficient for range queries and high-cardinality columns. Bitmap indexes excel at equality queries on low-cardinality columns but are less efficient for range scans because bitmaps represent discrete values.

❓ Reasoning

expert

Impact of Frequent Updates on Bitmap Indexes

Why are bitmap indexes generally not recommended for tables with frequent insert, update, or delete operations?

A Because updating bitmaps requires locking and can cause contention, leading to slower write performance.

B Because bitmap indexes do not support any form of concurrency control.

C Because bitmap indexes automatically rebuild the entire index on every update.

D Because bitmap indexes store data redundantly, causing excessive disk usage on updates.

💡 Hint

Think about how bitmaps are stored and updated when data changes.

Bitmap indexes use bit arrays that need to be updated when rows change. This often requires locking parts of the index, which can cause contention and slow down write operations. Hence, they are best for mostly read-only or infrequently updated tables.