Introduction

Last Wednesday, we were talking about how to update blocks when we perform insert, delete, update record operations.
We then started considering indexes as a means to speed up queries on relations and to avoid having to do full table scans when searching for a record with a particular key value.
We considered storing relations in sequential files.
Sequential files were files sorted by a key field, allowing us to do binary search for a key to look up a particular record.
We then looked at dense indexes (one index entry/record).
Today, we continue talking about dense indexes, giving an example that we didn't get to last day, before considering sparse and other index structures.

Dense Index Example

Suppose `R` had 1,000,000 tuples and 10 fit into a block of 4096 bytes.
So R takes 100,000 blocks, that is, 400 megabytes to store.
If the key is 30 bytes long and the record pointer 8 bytes then around 100 index entries can fit into a block. So the index takes roughly 40 megabytes (10,000 blocks) to store.
This probably fits into main memory. If not, have only log (10000), roughly 13 or 14, accesses to look an entry.

Sparse Indexes

These take less space but are a little slower than dense indexes.
Idea is we no longer have an index entry for every record, instead we have one entry per block of the original file:

Illustration of block structure of a sparse index

Sparse Index Example

If `R` was 100,000 blocks, each storing 10 records as before, and again could store 100 index entries in a block, then would only need 1000 blocks (4MB) to store this index.
On the other hand, with a dense index could answer questions of the form: "Is `K` in the file?" using only the index, without having to look up the block containing `K` in the file itself.

Multi-Level Indexes

Since a sparse index itself can still take several blocks, one could imagine using a sparse index on a sparse index to look up the data.
In our previous example, the 1000 block index could have a 10 block sparse on it to help look up the correct block.
Multi-level indexes are fast, but can be a pain to update.

Indexes with Duplicate Search Keys

It is possible that more than one record has the same search key value in a sequential file.
For example, if we had sorted on Salary, two people in an Employee table might have the same salary.
One might modify a dense index to handle this by having one reference for each search key value and have it point to the first record with that value.
To find a record we then look up the search key value in the index and do a linear search of the records with this value.
This might still sometimes be called dense index, although the term clustering index is also used for this situation.
If we have a sparse index, we don't have to change anything to handle duplicates.

Indexes and Data Modification

When we change the data file, we also need to modify index.
What will happen depends on whether the index is dense or sparse.

Action on file	Dense Index	Sparse Index
Create Blocks	none	insert if sequential
Delete Blocks	none	delete if sequential
Insert	insert also into index	update(?)
Delete	delete also from index	update(?)
Slide	update	update(?)

Avoiding Overflow Blocks, Preventing Row Migration

The code to implement database relations is simpler/faster if we can avoid having to use overflow blocks and/or perform row migration.
Oracle has two parameters which are useful in avoiding overflow blocks and preventing row migration: PCTUSED and PCTFREE:
- PCTFREE - (defaults to 10) percentage of a block that is reserved for future updates to existing rows.
- PCTUSED - (defaults to 40) percentage full a block removed from the free blocks list must fall below to be added back to the free blocks list.
These values can be specified in a create table, create index, or alter table statement. For example,
```
CREATE TABLE SOME_TABLE_NAME ( /*def of columns */ ) PCTFREE 15 PCTUSED 75;
```
Postgres and Microsoft SQL Server have a concept FILLFACTOR which corresponds to 100 - PCTFREE, but SQL Server doesn't have a notion corresponding to PCTUSED.
DB2 allows one to specify the analogous values PCTFREE (for table and index) and MINPCTUSED (for an index only).
mysql, and sqlite don't have an analogous concept.

Quiz

Which of the following statements is true?

Since each record has its own header, DB blocks typically don't have headers.
If a variable length record is updated to a shorter record, we can overwrite the old record and add unused space to an available space list.
Any record that cannot fit into a block is called a blob.

Secondary Indexes

Up until now we have been considering primary indexes.
These are indexes which are on the search key of a sequential file.
Secondary indexes are indexes on fields which are not sorted in the file.
Secondary indexes are always dense and will often have duplicates.

One can create indexes in SQL using commands like:

CREATE INDEX MY_INDEX ON MY_TABLE(MY_FIELD_1, ..., MY_FIELD_n);

Design of Secondary Indexes

Notice that although the indexed field is not sorted in the file, it is sorted in the index.
Secondary indexes are still usually much smaller than the original file size.

Applications of Secondary Indexes

In addition to allowing indexes on non-sorted fields of a table, secondary indexes have other uses:
- They might provide an index on a primary key if the file was not sequential but rather a heap (i.e., unsorted).
- They allow indexes to be had on a horizontally partitioned relation (a large relation which has been split into several tables by some row attribute, for example by letter in last name) or clustered file/table. (two related tables which shared attributes and which are stored near each other on disk).

As an example of a clustered file consider the tables from the Garcia-Molina, et al:

Movie(title, year, length, genre, studioName)
Studio(name, address, presidentCertificateNo)

Suppose a common query is:

SELECT title, year 
FROM Movie, Studio 
WHERE producerCertificateNo = zzz AND Movie.studioName = Studio.name;

If we think this is the main query, we have we might cluster the Movie records on disk as (hence, Movie would be a clustered file):

Indirection in Secondary Indexes

The scheme for secondary indexes previously presented wastes space because we often have to repeat the same key value multiple times.
We can use indirection to minimize this waste:
Notice we are saving the space needed to write down the key value several times.
Instead, we need only the space to write one db address to store an additional index record with the same key value.

Document Retrieval and Inverted Indexes

An inverted index provides a mapping between terms and their locations in a text collection C.
For web search, a text collection is typically split into documents and the mapping might only say the document rather than the byte offset in the document at which the term occurs.
Such inverted indexes are usually secondary indexes and employ the kind of indirection mentioned on the last slide.
The data structure used to store the possible key values is called a dictionary.
The data structure used to contain the list of locations where a particular term occurs is called a posting list.
A single entry in a posting list is called a posting.
Often an inverted index stores only stem words for a given word/term in the document. Ex: storing dog for dogs, jump for jumping.
Some stop words like "a" and "the" might not be included in the inverted index.
To create a fulltext index for a DBMS like MYSQL which supports them, we can use a command like:
```
ALTER TABLE  my_table 
ADD FULLTEXT(column1, column2,..)
```
If our SQL query has a LIKE clause on the columns in question the index will be used.
Information on using stopwords in MySQL can be found on the MySQL reference article on full-text stopwords.

Indexes and Record Modification - Secondary Indexes

Outline