15.00 - NUSI Row Structure - Teradata Database

Teradata Database Design

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

NUSI Row Structure

The subtable structure of a non‑geospatial NUSI row is described by the following graphics. See “Row Structure for Packed64 Systems” on page 753 and “Row Structure for Aligned Row Format Systems” on page 755 for additional information.

The number of base table rows that can be referenced in a NUSI row is limited by the maximum row size for the system and the length of the secondary index value. See “NUSI Sizing Equation” on page 864 for more information about sizing NUSI subtables.

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

For geospatial indexes, the Secondary Index Value field contains the Minimum Bounding Rectangle (MBR) for the object being indexed. Unlike other Teradata Database indexes, geospatial NUSIs are maintained in a Hilbert R‑tree structure that sits on top of the Teradata file system rather than as rows in a relational index subtable.

Scalar NUSIs contain index key values that are equal to their base table counterparts, while geospatial NUSIs contain index keys that are only an approximation of their base table counterparts (other than case of characters if the ALL option is not specified). Leaf rows in the Teradata implementation for geospatial R‑trees have the form MBR (index key), base table row ID list.

For more information about Hilbert R‑trees and geospatial NUSIs, see SQL Request and Transaction Processing.

Packed64 Format NUSI Subtable Row Structure for a Nonpartitioned Base Table

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

Packed64 Format NUSI Subtable Row Structure for a Partitioned Base Table

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

Aligned Row Format NUSI Subtable Row Structure for a Nonpartitioned Base Table

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

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

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

Each rowID in the base table rowID list consumes an additional 2 or 8 bytes over rowIDs in NUSI subtable rows for an nonpartitioned base table or join index. This is because either 2 or 8 additional bytes are required to store the internal partition number of the base table rowID.

Definitions for NUSI Row Structure Fields

 

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 NUSI 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 non-unique secondary index value.

     Uniqueness value

     4

 

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

Overhead

2

2 single‑bit flag fields indicate whether the internal partition number is 0.

Secondary index value

up to (65,524) - (table rowID list bytes)

Defines the column values for the nonunique 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 list

  • 8 per nonpartitioned base table row.
  • 10 per 2‑byte partitioned base table row.
  • 16 per 8‑byte partitioned base table row.
  • Defines the row IDs for the rows on the same AMP to which this non-unique index points.

    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.

    When the system adds new base table Row IDs to a NUSI Row ID list, it continues to write from the end of the row. This means that it overwrites the previously existing trailing alignment pad bytes with the newly added entries to the list of Row IDs. Note that because the two alignment pads serve independent requirements, there might be pad bytes at the beginning of the row ID list as well as at the end of row, but there are never any pad bytes in the middle of the Row ID list.