Outline

• Secondary Indexes
• Inverted Indexes
• Quiz
• Multi-Column Indexes

Introduction

• We have been discussing the internal structures used by DBMSs to physically store databases.
• We talked about different record formats and then looked at how records can be stored in different kinds of files such as heap files, sequential files, and random access files.
• We distinguished between primary file organization, with a search key used to organize a file together with potentially an index on top of this, and secondary file organization, which might involve additional indexes not on this organizing index.
• We looked at kinds of indexes such as dense, sparse, clustered and then started talking about list data organization.
• Today, we start by talking about secondary indexes, inverted files, and other kinds of indexes.

Secondary Indexes

• A secondary index is based on an attribute or attributes that are not used as an ordering criteria of the actual data file.
• Its search key can be a key or it can be a non-key collection of attributes.
• In the former case, the search key uniquely identifies a record, in the latter it does not.
• An index entry is a tuple (key, record offset, block pointer).
• As we can see above, we often have to have one index entry per file record. This still can result in a smaller index than a file if the index entry is smaller than the underlying record 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.
• The above setup is called an inverted file.

Document Retrieval and Inverted Indexes

• An inverted index provides a mapping between terms and their locations in a text collection C.
• The name is similar to inverted file, but it is a slightly different concept.
• 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.

Inverted Indexes and Column-Oriented Databases

• Recall in a column-oriented database for a table a particular value, say 20, of a column, say Price, has associated with it a list of all the id's of the table with that price.
• This is very much the same as a posting list for a term, say aadvark, having all the document id's of documents with that term.
• In both cases, the underlying file is typically organized by increasing integer id.
• To compress such index's columns, we need techniques for compressing increasing sequences of integers.
• This is a whole area of active research.
• We make a couple quick observations: if we have a sequence `a_0 < a_1 < a_2 < ... < a_n`, then `b_0 = a_0`, `b_(i+1) = a_{i+1} - a_i` is a sequence of positive integers general of smaller size than the original sequence.
• The `b_i`'s are called `Delta`-lists and can be used to reconstruct the original `a_i`'s.
• Further, to code a value `b_i` we can first write down `b_i`'s length -1 in unary using 0's followed by `b_i` in binary. This is a so-called `gamma` -code.
• For example, number 5 would be encoded as 00101, 16 would be encoded as 000010000.
• Writing posting lists using a delta-list that is written using a code like a like a `gamma` -code is called gap-compression.

Quiz

Which of the following is true?

1. Sequential files are organized files of records in increasing or decreasing order of a search key .
2. Embedded identification records are the same thing as slotted pages.
3. A dense index is an index where we have exactly one entry per block of the original file.

Multidimensional Data

• All indexes considered so far are on one field. Say salary.
• We now consider indexes and file structures for more than one field. Say first name, last name.
• There are several techniques for such indexes:
  • grid-files
  • partitioned hash functions
  • multiple key indexes
  • kd-trees
  • quad trees
  • R-trees.
• We will give a flavor of these by considering kd-trees.
• These indexes will serve our first example of search trees.

Application Needing Multidimensional Data

• Geographics Information Systems
  • Store maps. Might include objects like points, houses, roads, etc... Or instead of maps, might store circuit layouts...
  • Types of queries:
    • Partial match query - ask for all points matching certain values in some specified dimensions
    • Range query - ask for all points between range of values
    • Nearest neighbor queries - closest point to a given point
    • Where am I query -- given a point, what object am I in?

More Applications

• DataCube
  • These are often used by decision support applications where they are used to analyze information to better understand a companies operations.
  • For example, a chain of stores might collect with each sale: the date and time, the store of purchase, the item, the color, and the size.
  • This information could be viewed as a point in a mulitdimensional space and one might want to quickly answer aggregating queries like: number of red ties sold in each store in each month of 2005.

SQL for Multidimensional Queries

• Might have a table of Points with `x` and `y` attributes.
• A query might look for the closest point to `(10,20)`:

  SELECT * FROM Points p WHERE NOT EXISTS(
  SELECT * FROM Points q
  WHERE (q.x-10.0)*(q.x-10.0) + (q.y-20.0)*(q.y-20.0) < 
      (p.x-10.0)*(p.x-10.0) + (p.y-20.0)*(p.y-20.0)
  );
  

• Another possible query: Have a table of Rectangles and ask for all tuples containing a point.

Using Conventional Indexes

• To do a range query like find all points between `300 lt x lt 400` and `500 lt y lt 600`, we could have indexes on `x` and `y`.
• Suppose 1/10 of records satisfy the above condition on `x` and 1/10 satisfy the condition on `y`.
• So 1/100 of points are in the rectangle.
• Suppose 100 is the number of records that can be held in a block. If the original file had 1,000,000 records, then it would fit in 10,000 blocks
• We could do a range query on x to retrieve records pointers for x in desired range and do the same for y.
• We can then intersect these lists of pointers... And look up each record.
• Unfortunately, each record is likely to be on a different block.
• So we'd have to look up (`1/100 times mbox(# of records)`) blocks to do this. i.e., 10,000 blocks.
• But this is the size of the whole file
  ...so doing a range query on `x` or `y` hasn't saved us anything over a table scan.

Multiple Key Indexes

• Multiple key index is a kind of index on more than one attribute.
• We will consider the two attribute case -- the more than two attribute case is implemented in a similar way.
• In two key index, we first make a tree index on the first attribute.
• The leaves of this tree are possible values for this attribute.
• The pointer followed from this value points to a tree index on the second attribute.

Uses of Multiple-Key Indexes

• Supposed we have such an index on Age and Salary.
• If, in a query, we specify both a fixed Age value (say 35) and a fixed Salary (say 20K), the results can be fast.
• However if we wanted all Age's for a given Salary, such an index would be slow if Age was the first top index.
• Multiple key indexes work well for range queries and nearest neighbor queries. (Treat nearest neighbor as a range query)

kd-trees

• kd-trees are a generalization of binary search trees.
• Each non-leaf node has some attribute `A` and some value `V`.
• Leaf nodes contain record pointers to actual records.
• The tree satisfies that beneath a non-leaf node on attribute `A` with check `V`, the records to the left of the node are less than `V`, those to the right are greater than or equal to `V`.

Operations on kd-trees

• Basic search in a kd-tree is to follow the nodes down the tree, following the branch on an attribute by choosing the side that matches the record we are searching for.
• Partial match search involves following both paths out of a node if do not know its value.
• Similarly, for a range query, if the range straddles a query we have to follow both branches.
• Nearest neighbor search is treated as a special kind of range query.
• To do an insertion:
  • We first do a basic lookup search to find the leaf node the record should be inserted into.
  • If this node is not full, we add the record there.
  • If not, we choose the attribute that has the most possible values amongst those in the leaf node.
  • We then split the node in two based on the median value of this attribute and create a new interior node using this attribute and value.
• To optimize kd-trees for disk rather than main memory, rather than split on one value `V` at a node, split `n` ways based on block size.