15.00 - Hashed Row Access - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Hashed Row Access

Master Index

The master index is a memory-resident file system data structure that contains pointers to every cylinder index on a given AMP in entries referred to as Cylinder Index Descriptors, or CIDs. When a cylinder does not contain data, it is not listed in the master index.

Each master index entry contains the following data sorted on table ID and rowhash.

  • Lowest table ID in the cylinder
  • Lowest partition/rowhash/uniqueness value on the cylinder (associated with the lowest table ID)
  • The partition value is 0 for NPPI tables and nonpartitioned NoPI tables.

  • Highest table ID in the cylinder
  • Highest partition rowhash (not the uniqueness value) value on the cylinder (associated with the highest table ID)
  • The partition value is 0 for NPPI tables and nonpartitioned NoPI tables.

  • Cylinder number
  • The file system uses the table ID and rowID (or partition, partition/rowhash combination, or rowhash value) for the row being retrieved to scan the master index for the desired cylinder number.

    As an example of how a table ID and rowhash are used to scan the master index, consider the following query.

         SELECT *
         FROM employee
         WHERE emp_no = 7225;

    For this example, assume that the decimal rowhash value for 7225, the primary index value for the row in question, is 266 and the table ID for the employee table is 100.

    Consider the following set of contrived master index entries, assuming all tables are NPPI tables, so their partition number is 0.

     

    Lowest Table ID

    Starting RowID

    Highest Table ID

    Highest Rowhash Value

    Cylinder Number

    078

    0 + 58234 + 0001

    081

    0 + 58332

    204

    100

    0 + 00017 + 0001

    100

    0 + 00073

    037

    100

    0 + 00102 + 0001

    100

    0 + 00687

    169

    100

    0 + 00785 + 0001

    100

    0 + 00991

    777

    100

    0 + 01087 + 0001

    100

    0 + 01116

    802

    100

    0 + 03662 + 0003

    100

    0 + 03712

    117

    100

    0 + 04123 + 0001

    100

    0 + 04255

    888

    100

    0 + 05974 + 0001

    100

    0 + 06785

    753

    100

    0 + 07353 + 0001

    100

    0 + 07834

    477

    123

    0 + 00543 + 0001

    123

    0 + 00786

    529

    The actual cylinder number under the column heading Cylinder Number is a 16‑digit hexadecmial value, but to make this example more readable, smaller integer values are used.

    The process for finding the required cylinder block is as follows.

    1 The file system performs a binary search on the master index using the target tableID and rowID as the search key.

    2 This results in locating the cylinder ID for cylinder number 169.