Outline

• Indexes with Duplicate Search Keys
• Indexes and Data Modification
• In-Class Exercise
• Secondary Indexes

Introduction

• On Monday, we were talking about how to update blocks when we performs 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) 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.

In-Class Exercise

Suppose we pre-allocate 10 blocks for a unsorted relation. Each block can typically hold five variable sized records. We are using PCTFREE=20, PCTUSED=40. Apply a sequence of operations to this relation/blocks where an insert has probability 5/6, and a delete has probability 1/6 (use dice). Keep going until either all blocks are at their limit or until a block gets re-added to the free block list. Show the state of the 10 blocks after each adding/deleting of records.

Post your solution to the Feb 12 In-Class Exercise Thread.

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.