Outline

• Indexes with Duplicate Search Keys
• Indexes and Data Modification
• Quiz
• Secondary Indexes
• Start B-Trees

Introduction

• Last week, we were talking about how to update blocks when we performs insert, delete, update record operations.
• We then started considering indexes as 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) and sparse indexes (one index entry/block).
• Finally, we looked at multi-level indexes.
• Today, we start by considering what to do if the search key of an index is not unique.

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.

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;
  

• Microsoft SQL Server has 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).
• Postgres, mysql, and sqlite don't have an analogous concept.

Quiz

Which of the following statements is true?

1. DB Logical Addresses usually contain information such as the track number of the physical location of database record on disk.
2. Reading a DB block into memory and converting all the DB addresses in it to memory addresses is an example of pointer swizzling.
3. A sparse index on a table always has one index entry for every record in the table.

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, producerCertificateNo)
  Studio(name, address, presidentCertificateNo)
  

  Suppose a common query is:

  SELECT title, year 
  FROM Movie, Studio 
  WHERE presidentCertificateNo = 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.

B-trees

• As we have already seen, multi-level indexes can speed up the retrieval of data.
• B-Trees are a family of data structures that allow us to maintain such kind of indexes by supporting inserts, deletes, and updates.
  • They automatically maintain the correct number of levels for the file size in use.
  • B-tree index blocks are used so that they are always between half and completely filled.

The Structure of B-trees

• A B-tree is a balanced tree whose nodes are blocks. That is, every path from the root to a leaf is of the same length.
• A B-tree is characterized by a parameter `n` which depends on the block size.
• Within a node a B-tree stores `n` search key values and `n+1` pointers. (pointers for internal nodes always point to one level lower in the tree)
• Key values are nondecreasing in going from left to right with tree at the same level
• We assume that the root will always use at least two of these pointers (unless we have an index with only 1 record).
• At a leaf, the last pointer points to the block to right. At least half of the pointers in a leaf are used and the pointers point to actual records.
• At least half of the pointers in are used in an internal node.