Hash indexes in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does a hash index improve data retrieval?

Imagine you have a large phone book and want to find a person's phone number quickly. How does a hash index help speed up this search in a database?

A It scans all records sequentially until it finds the match.

B It organizes data in a sorted order to allow binary search.

C It uses a hash function to directly locate the data's storage location.

D It duplicates data in multiple tables for faster access.

💡 Hint

Think about how a locker system uses a code to find the exact locker without checking all lockers.

A hash index applies a hash function to the search key, which calculates the exact location where the data is stored. This direct access avoids scanning through all records, making retrieval very fast.

📋 Factual

intermediate

What is a limitation of hash indexes?

Which of the following is a known limitation of hash indexes in databases?

A They cannot handle exact match queries efficiently.

B They always use more storage space than other indexes.

C They require data to be stored in sorted order.

D They do not support range queries effectively.

💡 Hint

Think about whether you can find all phone numbers between 1000 and 2000 using a hash index.

Hash indexes are excellent for exact match queries but do not support range queries well because the hash function distributes keys randomly, losing any order information.

🚀 Application

advanced

Choosing hash index for a query

You have a database table with millions of user records. You want to quickly find a user by their unique ID. Which indexing method is best suited?

A Use a hash index on the user ID column.

B Use a full-text index on the user ID column.

C Use no index and scan the entire table.

D Use a B-tree index on the user ID column.

💡 Hint

Consider which index type is optimized for exact matches on unique keys.

A hash index is ideal for exact match lookups on unique keys like user IDs because it provides direct access to the record without scanning or traversing a tree.

🔍 Analysis

advanced

Why might a hash index cause collisions?

Hash indexes use hash functions to map keys to locations. What happens when two different keys produce the same hash value?

A A collision occurs, and the database must handle it to avoid data loss.

B The database ignores one of the keys and stores only the other.

C The hash function automatically changes to avoid collisions.

D The keys are merged into a single record.

💡 Hint

Think about what happens if two people have the same locker number in a locker system.

When two keys produce the same hash value, a collision occurs. The database must use techniques like chaining or open addressing to store both keys without losing data.

❓ Reasoning

expert

Comparing hash index and B-tree index performance

Consider a database with frequent exact match queries and occasional range queries on a large dataset. Which indexing strategy balances performance best?

A Use only a B-tree index because it supports both exact and range queries.

B Use a combination: hash index for exact matches and B-tree for range queries.

C Use only a hash index because it is fastest for exact matches.

D Use no index to avoid overhead.

💡 Hint

Think about which index type is best for each query type and how combining them might help.

Hash indexes excel at exact matches but fail at range queries. B-tree indexes support both but are slower for exact matches. Combining both indexes allows optimized performance for each query type.