This content has been marked as final. Show 1 reply
A b-tree index supports ordered and range queries as well as exact match queries. This is a huge benefit of b-trees. For example, if you're looking for a list of names and phone numbers sorted by last name (a very common "cellphone" query), a btree index can support that very efficiently; a hash index would not be very useful. If you're looking up a specific record, then hash index or b-tree index would work equally well.
B-tree index is a very efficient data structure in terms of IO. Depending on the index node size and key size etc., a b-tree node can contain several hundred entries, resulting in a very high fanout. Typically, a three level index can support millions of records. Also, the internal nodes of the tree will be cached in memory - consequently, you don't incur too many IOs when accessing via b-trees.
Your general observation about hash indexing needing less memory than b-trees is correct; however, in practice, the difference is usually not noticeable; further, the b-tree is a much more general indexing mechanism and those benefits usually outweigh IO considerations.
There's a wealth of literature on comparisons of b-trees vs hash indexing, if you're interested in exploring this in more detail.
Hope this helps.
Thanks and warm regards.