Skip to main content
1 of 2
Michael Green
  • 25.1k
  • 13
  • 53
  • 98

Consider the case of a single row lookup .. where key_column = some_value, a very common case. Having retrieved the block how does the DBMS find the specific key? Without some structure the only option is to scan all keys on that block, which may number in the thousands, an O(n) operation. If the keys in the block are ordered a binary search can be used which is O(log n). For 1,000 keys in the block a scan will, on average, touch 500 rows and a binary search 10. Much performanter.

Similarly with range scans, another frequent case. Having done 10 steps to find the first matching key all other matching keys must necessarily be adjacent since the keys are sorted.

You are correct about the additional work required during insert. It is an overhead. A designer of DBMS must consider the whole workload her system is likely to see. A row is only ever inserted once; it will be read a great many times. On balance it is better to optimise for read performance over insert.

That said, insert optimisations are possible. One is to only store the index keys in sorted order together with a pointer to the remainder of the data elsewhere in the block. This is known as a slot array. These pointers can be small since they only have to reference within the block. During insert only these (key,pointer) pairs are moved around. The bulk of the data is not repositioned during insert. As the block must be in RAM to be altered all the slot array entries above where the new row is going to go can be shuffled across in a single, efficient operation.

Michael Green
  • 25.1k
  • 13
  • 53
  • 98