15.00 - Hash and Join Index Row Structure for Aligned Row Format Systems - Teradata Database

Teradata Database Design

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

Hash and Join Index Row Structure for Aligned Row Format Systems

The hash and join index row structure diagrams for aligned row format systems differ from those of packed64 format systems by having as many as five additional pad byte fields (depending on the data types of the columns defined for Index Value) to ensure row alignment on an 8‑byte boundary. The following table lists each of these pad fields and explains their purpose:

 

                     Pad Byte Field Name

                                 Purpose

VARCHAR Offsets Array Alignment Pad Bytes

Aligns VARCHAR offsets array at a 2‑byte boundary.

Fixed Length Column Alignment Pad Bytes

Aligns fixed length columns.

Nullable Length Column Alignment Pad Bytes

Aligns nullable length columns.

VARCHAR Column Alignment Pad Bytes

Aligns variable length columns.

Trailing Pad Bytes

Aligns entire row on an 8‑byte boundary.

The index columns in a row compressed hash or join index row are stored in packed64 format, adjusted for aligned row format systems by a field of alignment bytes trailing field1 and another field of alignment bytes trailing field2 (if necessary to make the entire row align on a modulo(8) boundary). When you row compress a hash or join index, the column_2_name value set for each row in the index is stored as a minirow. Each minirow consists of a byte length field, an optional presence bits array field, and the column_2_name value set for the minirow.

Teradata Database does not support PPIs for hash indexes, row‑compressed join indexes, or column‑partitioned join indexes.

Aligned Row Format Structure for an Uncompressed Hash or Join Index With an Nonpartitioned Primary Index

The following graphic illustrates the basic structure of a Teradata Database row from a non‑row compressed hash or join index with an nonpartitioned, or standard, primary index:

Aligned Row Format Structure for an Uncompressed Join Index With a Partitioned Primary Index and 65,535 or Fewer Combined Partitions

Teradata Database does not support PPIs for hash indexes.

The following graphic illustrates the basic structure of a Teradata Database row from a non‑row compressed join index (hash indexes cannot have partitioned primary indexes) with a partitioned primary index:

The difference between this and the format of a non‑row compressed non-partitioned primary index row is the presence of a 2-byte partition number field at the beginning of the RowID (see “PARTITION Columns” on page 801. It is this field that generates the need for a BYTE(10) data type specification for a RowID. For nonpartitioned primary index tables, the partition number is assumed to be 0, so the rowID of an nonpartitioned primary index table is also logically BYTE(10) (see “ROWID Columns” on page 800).

Aligned Row Format Structure for an Uncompressed Join Index With a Partitioned Primary Index and More Than 65,535 Combined Partitions

Teradata Database does not support PPIs for hash indexes.

The following graphic illustrates the basic structure of a Teradata Database row from a non‑row compressed join index (hash indexes cannot have partitioned primary indexes) with a partitioned primary index:

The difference between this and the format of a non‑row compressed non-partitioned primary index row is the presence of an 8-byte partition number field at the beginning of the RowID (see “PARTITION Columns” on page 801. It is this field that generates the need for a BYTE(16) data type specification for a RowID.

Aligned Row Format Join Index Row Layout for a Compressed Hash or Join Index With an Nonpartitioned Primary Index

Consider the following example CREATE JOIN INDEX text:

     CREATE JOIN INDEX test AS 
       SELECT (a,b), (x,y) 
       FROM table1, table2;

Teradata Database packs columns a and b, labelled as the column_1_name list in the CREATE JOIN INDEX syntax diagram, in the last subfield of Field1 of a row compressed join index row the same way that index keys (an index key is the set of values stored as “the index” in a secondary index) are packed in the Field1 of a secondary index row (columns a and b are stored in the area labelled as Index Value in the row format graphic).

The system also stores the non‑row compressed index values (each instance of those (x,y) values that has the same (a,b) values as a minirow in Field2) in the area labelled as Index Minirow List in the row format graphic. These columns are labelled as the column_2_name list in the CREATE JOIN INDEX syntax diagram.

The region labelled as Index Value in the following row format graphic is the column_1_name value set for the row and should be interpreted as containing the requisite pad bytes depicted in “Aligned Row Format Structure for an Uncompressed Hash or Join Index With an Nonpartitioned Primary Index” on page 778). It is an abbreviation for the various data type orderings and offsets shown in detail in the row format diagrams for non‑row compressed hash and join index rows. The number of trailing pad bytes required by a given row is the number of bytes it takes to make the row end on a modulo(8) boundary.

When you row compress a hash or join index, the column_2_name value set for each row in the index is stored as a minirow. Each minirow consists of a BYTE length field, an optional presence bits array field, and a column_2_name value set (see “Aligned Row Format Structure for an Uncompressed Hash or Join Index With an Nonpartitioned Primary Index” on page 778 or “Packed64 Row Structure for a Row Compressed Hash or Join Index With an Nonpartitioned Primary Index” on page 776).

Aligned Row Format Join Index Row Layout for Compressed Hash and Join Indexes With a Partitioned Primary Index

Teradata Database does not support PPIs for hash indexes, row‑compressed join indexes, or column‑partitioned join indexes.