ITPEC FE Morning April 2021 Question 28
Among the search processes for the “Sales” table, which of the following is appropriate to set a hash index rather than a B+ tree index? Here, the column in which the index is set is shown inside <>.
Sales (form number, sales date, product name, user ID, store number, sales amount)
Explanation
The search for user ID '1001' — it is an exact-match search, the only kind a hash index serves.
A hash index applies a hash function to the key and jumps straight to the location of that value, so an equality search costs about one access however large the table. The hash scatters keys, though: neighbouring values land in unrelated places, so the index cannot find everything between two values or everything that starts with some characters. A B+ tree keeps the keys sorted in linked leaves, so it handles both equality and ranges.
Answer (d)
Why not others:
- •(a) — 100 dollars or more is a range condition; it needs keys in order, which a B+ tree has and a hash index does not
- •(b) — names beginning with 'DB' form a range of sorted values ('DB' up to just before 'DC'), again a B+ tree search
- •(c) — every date in the current month is a range from the first day to the last
Key rule: Use a hash index only for columns searched with =; any range, prefix or ordering needs a B+ tree.
Databases · AI-written, may contain errors
This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.