Outline

• Extensible Hashing
• Linear Hashhing
• Storage Engines for Various DBMSs
• In-Class Exercise

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 hashing with block-sized buckets, using external chaining of blocks to resolve collisions.
• Today, we are going to start by looking at two other hash-based index data structures.
• Then we will switch gears and apply what we have learned to understand the way data is organized in specific DBMSs.
• We begin by going back to the dawn of time...

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).

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 bbefore 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, splitting the block that the record would be added to.
  • 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 "hierarchal 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 arrange files in a DAG-like structure rather than tree-like (Network Model rather than Hierarchal 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.
• 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.

How Tables and Indexes are Implemented: MySQL

• MySQL was originally developed in Sweden by Michael "Monty" Widenius and David Axmark in 1994-1995 based on the msql DBMS from Hughes Technologies that had been created in 1993.
• The original MySQL AB company was sold to Sun in 2008, which in turn became part of Oracle in 2010.
• In 2009, around the time the Sun-Oracle deal was announced, Widenius forked the MySQL code to create MariaDB.
• MySQL/MariaDB supports several backend storage engines (InnoDB, MyISAM, MEMORY, ARCHIVE, etc) for tables which can be chosen using the data definition language (DDL).
• You can set the storage engine in a CREATE TABLE or change it using an ALTER TABLE, For example,

  CREATE TABLE TEST(I INT) ENGINE = MYISAM;
  ALTER TABLE TEST ENGINE = INNODB;
  

• Roughly the same Data Manipulation Language (DML) SQL can then be used to do select, insert, delete, updates regardless of engine, however, table capabilities, integrity, etc do vary with table type.
• As of Spring 2020, MySQL 8.0.x is the latest version of MySQL, and its default storage engine in InnoDB. Prior to version 5.5, the default and original storage engine was MyISAM.
• The 8.0 branch first appeared in 2018 and adds a NoSQL storage engine to tthe earlier 5.7.x branch.

MyISAM, InnoDB, Aria, XtraDB

• MyISAM tables are stored in three files that begin with the table name:
  • A .frm file gives the schema format for the table
  • A .MYD file holds the actual data as a sequential file.
  • A .MYI is used to store an index into the data file.
• InnoDB tables are stored in two files: a .frm and a .idb file, with the latter having the data.
• MyISAM (no longer under development) was not designed to be ACID compliant (needed for transaction) and could also be crash-prone, but was good in read situations.
• InnoDb was designed as ACID compliant successor to MyISAM and is now faster under most kinds of workloads. It was orginally developed by Innobase which was acquired by Oracle and folded into MySql.
• Aria is the MariaDB variant of MyISAM and it is still under development.
• XtraDB is the MariaDB variant of InnoDb and it is still under development.
• MySQL support different row formats for the data file and it supports different kinds of indexes for each of these engines.
• For example, one can specify the type of index in MySQL with a using clause:

  CREATE INDEX FOO ON BOB(A) USING BTREE;
  or
  CREATE INDEX FOO ON BOB(A) USING HASH;
  

How Tables and Indexes are Implemented: Sqlite

• Sqlite was originally developed by D. Richard Hipp in 2000 at General Dynamics for use in embedded systems for guided missile destroyers.
• Sqlite databases are stored as a single file and the SQL syntax and semantics were loosely based off Postgresql 6.5.
• Sqlite databases come in two main formats which are still seen in the wild, Sqlite 2, based on gdbm, (pre-2004 - don't use), and Sqlite 3.
• The Sqlite 3 database format was developed with partial support from Aol.
• A Sqlite 3 database is organized as a sequence of pages.
• The first page in such a file begins with a 100 byte header, followed by a B-tree page header, followed by the rest of the root node of sqlite_master B-tree table.
• The first 16 bytes of the 100 byte header contains the string "SQLite format 3\000".
• This is followed by a two byte number which specifies the page size, which must be a power of two between 512, and 65536 (if these two bytes contain the number 1).
• Two important properties stored in the 100 byte file header are the logging method used by the database, and the page number of start of free space list.

In-Class Exercise

Create a sqlite3 file foo.sqlite. Make a table foo in this database with a single integer column A. Insert three rows of your choice. Open this file in a Hex editor take a screenshoot of the start of your file (low-res). Determine the page size for your DB file.

Please post your solutions to the Feb 19 In-Class Exercise Thread.

Sqlite Page Types

• A Sqlite 3 page can be of one of five types:
  1. lock-byte page. This page (at most one, and only if database bigger than 1GiB) is for storage which may be used by the OS file system to handle file locking.
  2. free-list page. This page type is for pages that were at some point allocated for a Sqlite DB which through deletions have become empty/inactive are added to a list of free pages for reuse. free-list page are either trunk pages or leaf pages. Trunk pages are part of a linked-list referenced from the header as the start of the database. Each trunk page can point to 0 or more leaf pages.
  3. B-tree page. This page type comes in two sub-types: B-tree table and B-tree index page types, the former being used to implement database tables, the latter for indexes on tables. Leaf pages of B-tree tables attempt to store the record data itself in the leaf up to some fixed number of bytes; B-tree index always use pointers to records rather than attempt to store things in B-tree.
  4. payload page. This page type is used for pages that store the remainder of records that couldn't be fit into a B-tree table leaf node. Contains a pointer to next page in list if record still needs more pages.
  5. pointer map page. Pages of this page type exist depending on the vacuum settings (used as part of multi-view concurrency control we'll talk about later) set for the database in its header. Let `J` be the number of 5 byte pointers that can be fit in the usable space of a page. Let `k \geq 0`. If incremental vacuuming or auto-vacuuming is being used, then pages `k(J+1) + 2` in the DB will be pointer map pages, and page `k(J+1) + 2` will be responsible for holding reverse pointers (say from child to parent in a B-tree) for pages `k(J+1) + 3` through `(k+1)(J+1) + 1` that might help speed up the vacuuming process for unused records and pages.

sqlite_master B-tree table

• The sqlite_master table stores records which would conform to the following scheme:

  CREATE TABLE sqlite_master(
    type text,
    name text,
    tbl_name text,
    rootpage integer,
    sql text
  );
  

• For a given sqlite_master records, the semantics of its columns are:
  • type - one of table, view, index, or trigger, says what kind of object the record is.
  • name - internal name for this object.
  • tbl_name - name given to objectin create statement.
  • rootpage - first page of B-tree associated with this object
  • sql - SQL create statement for this item.
• In addition, to records for any user defined tables, indexes, views, and triggers, the sqlite_master will typically have records for the following internal tables:
  • sqlite_sequence table (used to keep track of auto-increment sequence)
  • sqlite_stat1 to sqlite_stat4 tables used to contain statistics for tables for query optimization.

In-Class Exercise

Please post your solutions to the Feb 19 In-Class Exercise Thread.