Which index type is most appropriate for fast exact-match lookups on very large tables?

• A. B-tree
• B. Bitmap
• C. Columnstore
• D. Hash

Answer: (D)

The key idea here is using a data structure that lets you locate records by their exact key in constant time. A hash index does exactly that: it applies a hash function to the search value and directly jumps to the bucket where the matching record should reside. This gives average constant-time lookups, making it the fastest option for exact-match queries on very large tables, since the time doesn’t grow with the table size.

B-trees keep keys in sorted order and provide logarithmic search time, which is excellent for a mix of point lookups and range scans, but it’s not as fast as hashing for a single exact value on huge datasets. Bitmap indexes work well for low-cardinality columns and complex predicate evaluation, yet they’re not ideal for high-cardinality, frequent-updates scenarios or fast single-record lookups. Columnstore is optimized for scanning large volumes of data across many rows, not for rapid exact-match lookups of individual records.

So, for fast exact-match lookups on very large tables, a hash index is the best fit, assuming the workload is dominated by equality searches and the hashing mechanism is well-managed.