NUSI Row Structure - Teradata Database

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 685 and “Row Structure for Aligned Row Format Systems” on page 687 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 770 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. The figure shows a row in a table without load isolation. If the table is load isolated and NUSI is defined WITH LOAD IDENTITY, an 8-byte RowLoadID is added to the Base Table Row ID.

Note: On a load-isolated table, a NUSI without a RowLoadID is possible if the user indicates WITH NO LOAD IDENTITY explicitly in the index definition.

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. The figure shows a row in a table without load isolation. If the table is load isolated and NUSI is defined WITH LOAD IDENTITY, an 8-byte RowLoadID is added to the Base Table Row ID.

Note: On a load-isolated table, a NUSI without a RowLoadID is possible if the user indicates WITH NO LOAD IDENTITY explicitly in the index definition.

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. The figure shows a row in a table without load isolation. If the table is load isolated and NUSI is defined WITH LOAD IDENTITY, an 8-byte RowLoadID is added to the Base Table Row ID.

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. The figure shows a row in a table without load isolation. If the table is load isolated and NUSI is defined WITH LOAD IDENTITY, an 8-byte RowLoadID is added to the Base Table Row ID.

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 nonunique 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.