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.

Practice

1. What is the primary purpose of a hash index in a database?

easy

A. To store data in sorted order

B. To speed up range queries

C. To compress data for storage

D. To speed up exact key lookups

Solution

1. Step 1: Understand the function of hash indexes

   Hash indexes convert keys into hash values to quickly find exact matches.

2. Step 2: Compare with other index types

   Unlike B-tree indexes, hash indexes do not support range queries or sorting.

3. Final Answer:

   To speed up exact key lookups -> Option D

4. Quick Check:

   Hash index = exact key lookup speed [OK]

Hint: Hash indexes are for exact matches, not ranges [OK]

Common Mistakes:

• Thinking hash indexes support range queries
• Confusing hash indexes with sorted indexes
• Assuming hash indexes compress data

2. Which of the following is the correct syntax to create a hash index on a column named user_id in SQL (assuming the database supports hash indexes)?

easy

A. CREATE INDEX idx_user ON users USING HASH (user_id);

B. CREATE HASH INDEX idx_user ON users (user_id);

C. CREATE INDEX idx_user ON users (user_id) HASH;

D. CREATE INDEX idx_user HASH ON users (user_id);

Solution

1. Step 1: Recall standard SQL syntax for hash indexes

   The correct syntax uses CREATE INDEX with USING HASH to specify the index type.

2. Step 2: Analyze each option

   CREATE INDEX idx_user ON users USING HASH (user_id); correctly places USING HASH after the index name and before the column list.

3. Final Answer:

   CREATE INDEX idx_user ON users USING HASH (user_id); -> Option A

4. Quick Check:

   Syntax for hash index = CREATE INDEX ... USING HASH ... [OK]

Hint: Use 'USING HASH' after index name to specify hash index [OK]

Common Mistakes:

• Placing HASH keyword incorrectly
• Omitting USING keyword
• Using non-standard syntax unsupported by SQL

3. Consider the following SQL query on a table with a hash index on email column:

SELECT * FROM users WHERE email = '[e-mail]';

What is the expected behavior of the database when using the hash index?

medium

A. It performs a range scan on the email column

B. It scans the entire table because hash indexes do not support equality

C. It performs a fast exact match lookup using the hash index

D. It returns an error because hash indexes cannot be used in WHERE clauses

Solution

1. Step 1: Understand hash index usage in equality queries

   Hash indexes are designed to quickly find rows matching an exact key value.

2. Step 2: Analyze the query condition

   The WHERE clause uses equality on the indexed column, so the hash index is used efficiently.

3. Final Answer:

   It performs a fast exact match lookup using the hash index -> Option C

4. Quick Check:

   Equality query + hash index = fast lookup [OK]

Hint: Hash indexes speed up exact equality queries [OK]

Common Mistakes:

• Thinking hash indexes do full table scans
• Confusing hash index with range scan
• Assuming hash indexes cause errors in queries

4. A developer created a hash index on the phone_number column but notices that queries with LIKE '%1234' are slow. What is the most likely reason?

medium

A. The hash index is corrupted and needs rebuilding

B. Hash indexes do not support pattern matching or partial searches

C. The database does not support hash indexes on numeric columns

D. The query optimizer ignores all indexes for LIKE queries

Solution

1. Step 1: Understand hash index limitations

   Hash indexes only support exact key lookups, not pattern matching or partial searches.

2. Step 2: Analyze the query pattern

   The LIKE '%1234' pattern searches for suffix matches, which hash indexes cannot optimize.

3. Final Answer:

   Hash indexes do not support pattern matching or partial searches -> Option B

4. Quick Check:

   Hash index ≠ pattern matching support [OK]

Hint: Hash indexes only speed exact matches, not LIKE patterns [OK]

Common Mistakes:

• Assuming hash indexes speed up all LIKE queries
• Blaming index corruption without evidence
• Thinking numeric columns can't have hash indexes

5. You want to optimize a database table for fast lookups on a customer_id column, but also need to efficiently query ranges of order_date. Which indexing strategy is best?

hard

A. Create a hash index on customer_id and a B-tree index on order_date

B. Create hash indexes on both customer_id and order_date

C. Create a B-tree index on customer_id and no index on order_date

D. Create no indexes and rely on full table scans

Solution

1. Step 1: Match index types to query needs

   Hash indexes are best for exact key lookups like on customer_id.

2. Step 2: Use B-tree indexes for range queries

   B-tree indexes efficiently support range queries, so use it on order_date.

3. Final Answer:

   Create a hash index on customer_id and a B-tree index on order_date -> Option A

4. Quick Check:

   Hash for exact, B-tree for range queries [OK]

Hint: Use hash for exact keys, B-tree for ranges [OK]

Common Mistakes:

• Using hash index for range queries
• Not indexing columns needed for fast queries
• Relying on full scans for large tables