Hashed Row and RowID Access - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

Hashed Row and RowID 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 rowID.

  • Lowest table ID in the cylinder
  • Lowest rowID (partition/rowhash/uniqueness value) on the cylinder associated with the lowest table ID. Partition indicates the internal partition number. For a PA or NoPI table, rowhash/uniqueness is the hashbucket/uniqueness.
  • The partition value is 0 for nonpartitioned tables.

  • Highest table ID in the cylinder
  • Highest partition/rowhash (not the uniqueness value) value on the cylinder (associated with the highest table ID). For a PA or NoPI table, rowhash is the hash bucket plus the high-order 12 bits of the uniqueness.
  • The partition value is 0 for nonpartitioned tables.

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

    Note: The file system treats PI, PA, and NoPI tables the same. The file system treats rowhash/uniqueness and hash bucket/uniqueness both as 64-bit values. There are a number of kinds of physical rows, such as table header, table row, subrow, container, and index.

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

         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 nonpartitioned tables, so their internal 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 hexadecimal 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 100 and the rowhash value of 7225 as the search key.

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