Introduction

We have been looking at the data structures for records, files, and indexes used in physical database design.
Today, we look how tables and indexes themselves are organized within a database into tablespaces and index spaces.
We then look at how queries are processed and optimized in such a setting.
Next day, we consider storage hardware.
We begin today by looking at how a particularly simple DBMS, Sqlite, is implemented.

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\00".
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.

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

Quiz

Which of the following is true?

An inverted file index is typically bigger than a standard dense secondary index.
The `k` chosen for a `k`d-tree in a database setting is usually related to the number of attribute values and pointers that can fit in a block.
A B-tree is a kind of balanced binary tree.

Database Organization and Table Spaces

A database in Sqlite is basically a file containing several tables.
Most relational DBMSs (Mysql, Postgres, ORACLE, DB2) are not single file based.
We typically have pool of storage devices on which the various components of our database can be stored.
A DBMS also typically has user databases together with a system database for managing all the meta data concerning the database itself.
Recall we can add a new user database to the set of names of databases stored in the system database using the SQL command:
```
CREATE DATABASE <some_database_name>
```
We can then use some syntax (for example, USE <some_database_name>) to specify which database our CREATE TABLE commands are intended for.
To get rid of such a database we use the DROP DATABASE command.
To be able to specify a connection between the database and the physical media on which its tables are stored we use the concept of a tablespace, a location to store tables.
Indexes may be stored in these tablespace or in separate index spaces.

Tablespaces and SQL

Tablespace can be specified in some RDBMSs (Postgres, Oracle, DB2) with the syntax:
```
CREATE TABLESPACE <some_name>
LOCATION 'some_folder';
```
This can be useful if we want some tables on SSD versus spin drive.
You can then specify the tablespace a database or table uses with a WITH clause:
```
CREATE DATABASE foo WITH TABLESPACE = blah;
```
By default, tables for foo above would be stored in the foo table space.

For certain large databases and tables it might make sense to split a table or index across many devices (Oracle example):

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 );

Since tablespaces can be on different devices this means portions of queries to such a table can be computed in parallel. This is called intra-query parallelism.
Notice if our DBMS manages two databases on different devices each in its our tablespace, then the queries to each can run in parallel. This is called inter-query parallelism
Most databases allow for fine grain control of the actual storage of tables in files. We won't discuss in detail, but to give a flavor, we point out in Mysql we can specify the storage engine in the CREATE TABLE statement:
```
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;
```

Index Design

Indexes are one of the most important tuning instruments available to a database designer because the available indexes directly influences decision made by a database system's query optimizer.

As indexes represent part of a systems internal data model, they are not part of the most recent SQL standard, although the syntax for creating an index is relatively similar across vendors:

CREATE [UNIQUE] INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME [ORDER] {, COLUMN_NAME [ORDER]})
[CLUSTER]

For example,

CREATE UNIQUE INDEX PRODNR_INDEX
ON PRODUCT(PRODNR ASC)

or

CREATE INDEX PRODUCTDATA_INDEX
ON PRODUCT(PRODPRICE DESC, PRODTYPE ASC)

The main reasons for creating an index are:
- To allow for efficient retrieval of rows according to certain selection criteria.
- To allow for efficient performance of join queries.
- To enforce uniqueness on a column value or combination of column values.
- To make it quick to look up rows according to a logical ordering.
- To fix a primary physical ordering of rows in a table (if unique keyword, rather than default cluster used)

Data Access Methods

We next briefly consider some techniques used by the DBMS to go from a SQL query to an access plan to find the data in the physical database files.
The basic steps from a query to an access plan is done in a component called the query processor, which consist of the following sub-components:
- DML compiler - this extracts DML statements from the host language
- query parser - parses the query into an internal representation, checking both for syntactic and semantic errors.
- query rewriter - which optimizes the query independent of database state by simplifying it according to a set of heuristics.
At this point this simplified internal representation must be mapped to access choices for its components.
Within a DBMS, it is the job of the query optimizer to choose from among several possible ways to retrieves the results of a query, the one that will be the most efficient.
To do this, most systems (in particular, cost-based optizimers) compute some estimate of the cost of performing different plans and choose the plan of least cost.
To estimate the costs of different kinds of access, the DBMS maintains statistical data in its catalog.

DBMS Statistical Data

Table-related data:
- number of rows;
- number of disk blocks occupied by the table;
- number of overflow records associated with the table.
Column-related data:
- number of different column values;
- statistical distribution of the column values.
Index-related data:
- number of different values for indexed search keys and for individual attribute types of composite search keys;
- number of disk blocks occupied by the index;
- index type: primary/clustered or secondary.
Tablespace-related data:
- number and size of tables in the tablespace;
- device-specific I/O properties of the device on which the table resides.

Estimating the Cost of a Query

We are not going to go too in depth into cost estimation, but we will look at a one table example.
First, we define some terminology:
- A query predicate specifies a selection condition with respect to some table attribute `A_i` in a query. (For example, CustomerID= 11349, or Gender=M or YoB > 1970).
- A filter factor `FF_i` of such a query predicate represents the fraction of the total number of rows that is expected to satisfy the predicate associated with attribute `A_i`.
- The query cardinality `QC` is the number of rows the query returns.
- The table cardinality `TC` is the number of rows in a particular table.
For a query over a single table, we can estimate the number of rows returned as:
`QC = TC times FF_1 times FF_2 times ... times FF_n`
If no additional information is known, then `FF_i` can be estimated as `1/(NV_i)` where `NV_i` is the number of distinct values of `A_i`.

Example Estimating the Cost of a Query

Consider the following query:

SELECT CUSTOMERID 
FROM CUSTOMERTABLE 
WHERE COUNTRY = 'UK'
AND GENDER = 'M';

Suppose there are 10000 row in CUSTOMERTABLE, i.e., TC=10000 and that there are 20 distinct countries and two distinct genders.
Then `FF_{mbox(COUNTRY)} = 1/20` and `FF_{mbox(GENDER)} = 1/2`.
In which case, we have:
`QC = 10000 times 1/20 times 1/2 = 250`.

Query Optimizer Example

Suppose now the query optmizer has a choice between two plans:
1. Retrieve all rows satisfying COUNTRY = 'UK' by means of an index on COUNTRY and filter the result by GENDER.
2. Retrieve all rows satisfying GENDER = 'M' by means of an index on GENDER and filter the result by COUNTRY.
In the first case, the index will get us `10000 times 1/20 = 500` rows to filter.
In the second case, the index will get us `10000 times 1/2 = 5000` rows to filter.
As the first case is much closer to the final answer it is more likely to be the efficient way of computing the query.

Physical Organization and Access Methods

Outline