Question

what are the advantages of a b+ tree over a hash index.

Answer 1

B-Tree Indexes

• B-Tree Indexes are suitable for range queries/range scans since the keys are ordered. For example, the following types of queries will benefit from a typical B-Tree Index structure on “SALARY” column.
  • “SELECT * FROM T WHERE SALARY>5000 AND SALARY<10000”
  • “SELECT * FROM T WHERE SALARY>5000”;
  • The result set for such queries will contain rows in sorted order since the index stores the keys in order.
• The above point about suitability of B-Tree Indexes for range scans is valid for both UNIQUE and NON-UNIQUE Indexes.
• It is not the case that B-Tree Indexes are not suitable point lookups. We can definitely use B-Tree indexes for point lookups but hash indexes are usually more efficient for such workloads.
  • The reason is extra I/O done in B-Tree indexes from the root block -> branch block -> leaf block to get to the data of interest whereas the cost of same operation with hash index is likely to be amortized O(1) provided the hash index is properly sized and handles collisions appropriately.
• B-Tree Indexes are efficient for both full-key and prefix-key matching queries.
• B-Tree indexes can be kept of optimal height to minimize the number of I/Os and the scalability problem mentioned above for hash indexes is not really a concern for B-Tree indexes.