---

Intro to indexing

---

• Introductory example
  • Consider the following query on relation stored on disk:

    Storing a relation on disk: Query: select * from R where a = 10;

    ---

  • Without further information, the only way to process the above query is:

    Read all blocks of the relation R Look for tuples with a = 10

    ---

  • More efficient processing:

    Maintain additional information on specific attributes (most useful ones, e.g., key attributes) in the relation

  ---

  ---

  ---

  ---

• Definitions
  • Search key:

    Search key = field(s) used to create the additional search information used to speed up a look up operation

    ---

  • Index file:

    Index file = a file that store records of the following format: +------------+----------------------+ | Search key | block/record pointer | +------------+----------------------+ Graphically: Note: The size of an index file is usually much smaller than the size of a data file

    ---

    ---

  • Note:

    The pointer is usually a block pointer So the index allow you to locate the block that contain the record quickly The record is found by a search operation after the block Because the search operation only access main memory, the search is relatively quick (no disk access)

  ---

  ---

  ---

  ---

• Types of indexes
  • There are 2 basic kinds of indexes:

    Ordered indexes The search keys are stored in sorted order in the index file Schematically: Index file content: Search key +------------------+--------+ | cat | p1 | +------------------+--------+ | dog | p2 | +------------------+--------+ | elephant | p3 | +------------------+--------+ ... Hash indexes The search keys are stored in hash buckets. Each hash bucket is stored as a list of blocks in the index file Schematically: Index file content: Bucket 1 (block 1) +------------------+--------+ H(cat) | cat | p1 | ----> +------------------+--------+ H(elephant) | elephant | p3 | ---> possible +------------------+--------+ overflow blocks Bucket 2 (block 2) +------------------+--------+ H(dog) | dog | p2 | ----> +------------------+--------+ ... ---> possible overflow blocks

  ---

  ---

  ---

  ---

• What you can achieve with an index
  • An index will achieve this effect:

    In other words:

    An index performs the following mapping: Search key value ======> List of blocks that contains the search key value

  ---

  ---

  ---

  ---

• Terminology: sequential file
  • Sequential file:

    Sequential file = a file whose records are sorted by some attribute(s) (usually its primary key)

    Example: a sequential file

    ---

    ---

  • Sort key:

    Sort key = field(s) whose values are used to sort/order the records in a sequential file

  ---

  ---

  ---

  ---

• Terminology: different kinds of index
  • Ordered index:

    Ordered index = an index file where the index entries are sorted (in the order of the search key)

    Example: an ordered index

    Note:

    Take sorted with a grain of salt We will discuss B-tree that store the keys in a tree structure There is an ordering of the keys in a B-tree, but the ordering is not sequential

    ---

    ---

  • Primary index

    Primary index = an ordered index whose search key is also the sort key used for the sequential file

    Example: a primary index

    ---

    ---

  • Secondary index

    Secondary index = an ordered index whose search key is NOT the sort key used for the sequential file

    Example: a secondary index

    ---

    ---

  • Index sequential file

    Index sequential file = a sequential file that has a primary index

    Example: an index sequential file

  ---

  ---

  ---

  ---

• Terminology: dense and sparse index
  • Dense index

    Dense index = the index file contains an index entry: +-----------+-----------------------+ | Search key | block/record pointer | +-----------+-----------------------+ for every value of the search key in the data file

    Example: a dense index

    Note:

    A secondary index is always a dense index:

    ---

    ---

  • Sparse index

    Sparse index = the index file's search entries +-----------+-----------------------+ | Search key | block/record pointer | +-----------+-----------------------+ does not contain every value of the search key of the data file The search key of a sparse index must be the sort key of the data file !!! A sparse index contains: one index for each (data) block

  • Organization of a sparse index

    ---

    ---

    ---

  • How to use a sparse index:

    Example:

    Look up the record with search key = 40

    Find the search key ≤ 40: Search in the block for search key 40:

    ---

    ---

  • Note:

    Only primary indexes can be a sparse index (Using the technique above: the sparse index stores the first key in each data block)

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• Multi-level index
  • Fact:

    An index file is also a data file            (It contains data !!!)

    Therefore:

    We can create an (primary) index on an index file !!!! Multi-level index....

    ---

  • Example: a multi-level index

    How to find the search key = D:

    ---

  • Note:

    We will soon discuss a dynamic multi-level index data structure: B-tree / B+-tree

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• Secondary index with indirection
  • "Normal" secondary index:

    Notice that:

    Every search key -- including duplicate are stored in secondary index (Secondary index is always dense)

    ---

    ---

  • Optimization trick:

    We can make the secondary index "less" dense by using indirection (The indirection pointers are sometimes called buckets)

    Example: