15.00 - USI Subtable Row Structure - Teradata Database

Teradata Database Design

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

USI Subtable Row Structure

See “Row Structure for Packed64 Systems” on page 753 and “Row Structure for Aligned Row Format Systems” on page 755 for additional information about the row structures of secondary indexes.

The internal partition number for a secondary index row is always 0, which is indicated by 2 flag bit settings.

The table header for the base table indicates whether it is partitioned or not and, if it is partitioned, whether it has 2-byte or 8-byte partitioning.

Teradata Database creates additional USI index subtable rows as they are needed.

Packed64 Format USI Subtable Row Structure for a Nonpartitioned Base Table

The USI subtable row structure for an index on an nonpartitioned base table is described by the following graphic.

The internal partition number for the base table row is implicitly 0 and is not stored.

Packed64 Format USI Subtable Row Structure for a Partitioned Base Table

The USI subtable row structure for an index on a partitioned base table is described by the following graphic.

Because the base table is a partitioned table, the Internal Partition Number field stores the internal partition number for the corresponding base table row in a physical 2‑byte or 8‑byte field.

Aligned Row Format USI Subtable Row Structure for an Nonpartitioned Base Table

The USI subtable row structure for an index on an nonpartitioned base table is described by the following graphic.

Because the internal partition number for nonpartitioned base table rows is always 0, the Internal Partition Number field pictured here is logical only, which is why it does not consume 2 bytes or 8‑bytes of overhead for the row like USI rows do for partitioned base tables.

Aligned Row Format USI Subtable Row Structure for a Partitioned Base Table

The USI subtable row structure for an index on a partitioned base table is described by the following graphic.

Because the base table is a partitioned table, the Partition Number field stores the internal partition number for the corresponding base table row in a physical 2‑byte or 8‑byte field at the beginning of the base table RowID field.

USI Row Structure Field Definitions

 

Stored Data

Length (bytes)

Function

Row length

2

Defines the number of bytes in the row.

If the row is aligned, this field includes any required pad bytes necessary to make the row length a multiple of 8 bytes. If the row is packed or packed 64, the row length does not include any extra pad bytes. Note that in this case, when space is allocated for the row, value is rounded up to a multiple of 2 bytes.

RowID

8

Defines the USI row uniquely for its subtable by combining its row hash value with a uniqueness value.

     Row hash

     4

 

Defines the output of the hashing algorithm, which is a unique (or nearly unique) value based on a mathematical transformation of the unique secondary index value.

     Uniqueness value

     4

 

Defines a system‑generated integer that ensures that the rowID is unique within a table.

Overhead (flag byte and 1st presence byte)

2

2 single‑bit flag fields (set to zero) indicate the internal partition number is 0 for the USI row.

Secondary index value

up to 65,524

Defines the column values for the unique secondary index.

Alignment pad bytes

between 0 and 7 bytes

Ensures that the Base Table Row ID field begins on a modulo(8) boundary. If the Base Table Row IDs field naturally aligns on a modulo 8 boundary, there is no field of alignment pad bytes at the end of the Secondary Index Value field.

Base table rowID

  • 8 bytes for an nonpartitioned base table row.
  • 10-16 bytes for a partitioned base table row.
  • Defines the rowID of the base table row this secondary index row identifies. This row is usually on a different AMP from its unique secondary index rowID.

    Because the rowID of a partitioned table row also contains the internal partition number for the row, it is 2 or 8 bytes longer than the rowID of a nonpartitioned table row.

     Internal  partition number

  • 0 for an nonpartitioned base table row.
  • 2 for a partitioned base table row with 2‑byte partitioning.
  • 8 for a partitioned base table row with 8‑byte partitioning.
  •  

    Defines the partition number for a partitioned base table row.

    If the base table row is from an nonpartitioned base table, the internal partition number is implicitly 0 and is not stored.

    The table header for the base table indicates whether it is partitioned or not and, if it is partitioned, whether it has 2-byte or 8-byte partitioning.

         Row hash

         4

     

    Defines the output of the hashing algorithm on the primary index value.

        Uniqueness value

         4

     

    Defines a system‑generated integer that ensures that the rowID is unique within a table.

    Trailing pad bytes

    0-7 pad bytes

    The trailing bytes are used to round up the row length of an aligned row to an 8 byte multiple. The trailing pad bytes are included in the row-length field (the 1st two bytes of the row). If the entire row naturally aligns on a modulo(8) boundary, there is no field of trailing alignment pad bytes

    For a NoPI or column-partitioned table, the 8 bytes of row hash and uniqueness value are treated as a hash bucket, which has either 16 or 20 bits, and as a 44‑bit uniqueness value. If the hash bucket is a 16‑bit bucket, the 4 bits between the 16‑bit hash bucket and the 44‑bit uniqueness value are not used.