Outline

• Extensible Hashing
• Quiz
• Linear Hashing
• Storage Engines for Various DBMSs

Introduction

• We have been looking at how database tables are implemented as records in files.
• We then considered various kinds of indexes that could be implemented.
• We have discussed B-trees and very briefly reviewed in memory hashing.
• Today, we are going to start by looking at hashing with block-sized buckets, using external chaining of blocks to resolve collisions.
• We then consider two other hash-based index data structures.
• Finally, we will switch gears and apply what we have learned to understand the way data is organized in specific DBMSs.

Secondary-Storage Hash Tables

• We next consider another way to implement secondary indexes which can often be as fast/faster than B-tree.
• I assume you have seen main memory hashing (W. W. Peterson, IBM Journal of Research and Development, 1957) before...
  • Set-up is have a hash function `h(K)` mapping keys into buckets `0, ..., B-1` and have some scheme to resolve collisions.
  • `h(K)` should be computable in time proportional to some polynomial (ideally, linear) of the length of `K` and `log B`. (Has to be proportional to `log B` as `h(K)` should be able to take any value in the range `0, ..., B-1`.)
  • Collisions should be rare enough that they can be resolved in `O(1)` expected time given some fill parameter of our hash table.
• If the hash table is in secondary storage, then we modify the above so that the buckets are each some number of blocks, initially, 1.
• If a block has too many records in it, then we use overflow blocks to handle any additional records hashing to this value.

Insertion, Deletion and Efficiency

• To insert we compute `h(K)`. If the labelled bucket `h(K)` still has room then we add a key pointer pair there. If not, we create an overflow bucket.
• To delete we compute `h(K)`, then search that bucket and any overflow bucket for the record pointer to the record to be deleted. Might consolidate blocks if some blocks becomes empty.
• Ideally, only one disk I/O is needed to find the record pointer we want. However, as the file grows we will tend to use overflow blocks more and more.

Extensible Hash Tables

• This data structure is due to Fagin, Nievergelt, Pippenger, Strong, ACM Transaction of Database Systems, 1979.
• We want to dynamically increase the number of buckets to avoid having to do more than one I/O per hash lookup.
• To do this we add an indirection level (which might be cached in memory), which is an array of pointers 0 to `2^i -1` for some `i` that the data structure keeps track of.
• Some of these pointers might point to same block.
• Each block records says in its header how many bits of hash used to get to it.
  
  The 1 in the corner in the image above.
• `h(K)` computes a `k` bit number for some `k >= i`. Alternatively, we could have a have function `h(K,i)` where `i` says number of bits we want.
• To look up `K`, first, compute `h(K)` using its first `i` bits. Then look up the corresponding indirection pointer and follow it to a bucket.

Insertion into Extensible Hash Tables

1. Compute `h(K)` look at first `i` bits.
2. Suppose this equals `x`.
3. Look up the `x`th entry in the indirection table, follow its pointer to a bucket.
4. If there is room insert into this bucket. (What would do for left hand image above, if inserted the record 0111)
5. If no room and the number of bits of hash value used to look up bucket way less than `i`, say `j`, divide entries in block `x` into two blocks according to the `j+1`st bit of the hash function. (What would do for left hand image above, if inserted both 0000, 0111).
6. If `j=i` then we double the number of indirection pointers. That is set `i:=i+1`. Each old pointer on `i` bits now becomes two pointers on `i+1` bits. Now we can go to 3. (The right hand image is what happens after inserting first 0000, 0111, then inserting 1000 -- which causes the doubling).

Quiz

Which of the following statements is true?

1. In Oracle terminology PCTFREE represents the percentage of a block that is reserved for future updates to existing rows.
2. In B-tree algorithms, the root node is not treated differently than any other node.
3. B-trees do not easily support range queries.

Linear Hash Tables

• Extensible hashing can be inefficient if:
  1. The number of records/block is small, we might have to set `i` reasonably high before can distinguish keys.
  2. We have to insert a lot and update pointers
  3. After a doubling of the indirection layer size, it can no longer fit in memory.
• Another technique, linear hashing (Litwin 1980), always keeps the number of buckets `n` so that at most 85% of each bucket is filled on average. A bucket on average should hold at most one blocks worth of records. It works as follows:
  • We use the low order `|~ log n ~|` bits from `h(K)` to determine the bucket to look at for a key.
  • Buckets still maintain in their header a prefix of the hash function as an identifier.
  • Overflow blocks are permitted but on average there will be much fewer than `1` such block per bucket.
  • When we insert we check if adding the record makes buckets more than 85% full.
  • If yes, we increase the number of buckets by one, if 1x is the bit pattern of the new block added, then we split the old block x.
  • We use the most significant bit of the used portion of the hash function to distinguish between these two blocks.
  • `n` increases in this case and hence so too might `|~ log n ~|` and the number of bits from the hash we use.

Example Linear Hash Table Insert Sequence

• Suppose we start with the following three records in a linear hash table:
  
  Notice the last (low order) bit says, which bucket the record is in.
• Adding a fourth record, 0101, makes the records/buckets ratio `2`. If a block holds at most two records, then the blocks would be more than 85% full, so we add a block. Now `n=3`, `|~ log n ~| = 2`. We get the diagram:
  
  Because we are using fewer than 4 blocks we know the suffix 11 is not in use. So 01 and 11 are still identified and so the records 0101 and 1111 are still in the same block.
• Adding a fifth record, 0001, makes the records/buckets ratio `5/3` which is less than 85% full, so add this record to an overflow block.
  
• If we insert `0111`, the records/buckets ratio is again 2, so we add a fourth block yielding:
  
  Notice at this point we move the record 1111 to the new block.

Dawn of Databases - COBOL and files

• According to Wikipedia, the first use of the term database was in 1962 in a report of the System Development Corporation.
• At that time, direct access storage (disk and similar devices) was just becoming available.
• CODASYL, the standards body for COBOL, came up with specs for how a COBOL program might access records within a file.
• It took a while for people to realize that file access should be abstracted from the particular programming language.
• It also took a while for people to abstract out the language to find records from the particular code in a programming language.

Dawn of Databases - IBM and ISAM

• As part of the Apollo program, IBM developed an early database known as IMS (instances of IMS are still in use to this day) in 1966.
• It used a "hierarchical model" rather than a the relational model we use today, and also introduced an early query language, DL/I.
• The way it stored records was highly influential for later DMBSs.
• It used variations on what is known as ISAM (Indexed Sequential Access Model).
• The basic idea of ISAM is to organize the data records in a sequential file according to key.
• For inserts, we use overflow blocks as needed.
• In the simplest design, on top of this we have a two level index: the top layer being a sparse index on (key,cylinder), then beneath this, for a particular cylinder, we would have an index on (key, block) that would map into the sequential file.
• An IMS database could be thought of as a set of such files, where records from a top level file could have key values of a lower file such that the set of files could be arranged into a tree-like structure.
• IBM had another product based on a General Electric (1964) database called IDMS, which arranged files in a DAG-like structure rather than tree-like (Network Model rather than Hierarchical Model).

How Tables and Indexes are Implemented: IBM, Ingres, Postgres, Oracle

• ISAM as described on the last slide is very specific to disk drives and even works best on old fashioned disk drives.
• IBM's next generation database was known as System R (developed in San Jose) and it officially came out in 1974.
• Its query language was called SEQUEL and was a precursor to modern SQL.
• As part of this work, IBM introduced a new storage format, VSAM, Virtual Storage Access Method.
• The idea was to abstract out the key components of the index part of ISAM and also to abstract out the implementation of the file, so that it no longer needed to be sequential.
• You can view VSAM as a container format with four possible payloads: Key Sequenced Data Set (KSDS), Relative Record Data Set (RRDS), Entry Sequenced Data Set (ESDS) (non-sequential) and Linear Data Set (LDS).
• The Key Sequenced Data Set which is essentially a multi-level index on a sequential file with overflow blocks, and so an abstracted out version of an ISAM file.
• At Berkeley, Michael Stonebraker and Eugene Wong became interested in the work on System R in 1973 and began building their own database system, Ingres.
• It supported four table types: Heap, Hash, ISAM, B-tree which specified the index on the key field of the table.
• Ingres influenced and spawned a number of products including Sybase SQL Server and Microsoft SQL Server.
• In 1979, Oracle created its first database and it was designed to be compatible as possible with System R so used a similar file implementation.
• In the early 1980s, IBM created DB2 as a successor to System R.
• In the 1980s, Postgres was developed as a successor of Ingres, it had a better recovery mechanism, support for geo-spatial indexes, and better concurrency control.