15.00 - Hash and Join Index Row Structure for Packed64 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 Packed64 Format Systems

Hash and join index rows on packed64 format systems do not have to align on 8‑byte boundaries. Because of this, their row structure is simpler than that of equivalent base table rows on aligned row format systems (see “Row Structure for Aligned Row Format Systems” on page 755).

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

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

 

Packed64 Row Structure for an Uncompressed Join Index With a Partitioned Primary Index

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

The difference between this and the format of a non-partitioned primary index row is the presence of a 2-byte partition number field at the beginning of the RowID. It is this field that generates the need for a BYTE(10) data type specification for a RowID. For nonpartitioned primary index indexes, the partition number is assumed by default to be 0, and is not stored.

Packed64 Row Structure for a Row Compressed Hash or Join Index With an Nonpartitioned Primary Index

The region labelled as Index Value in the graphic of the row format is the column_1 value set for the row. 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.

For example, consider the following example CREATE JOIN INDEX text:

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

The system 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 uncompressed 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.

Each minirow consists of a BYTE length field, an optional presence bits array field, and a column_2_name value set (see “Packed64 Row Structure for an Uncompressed Hash or Join Index With an Nonpartitioned Primary Index” on page 775 or “Packed64 Row Structure for an Uncompressed Join Index With a Partitioned Primary Index” on page 775).

 

Packed64 Row Structure for a Hash or Compressed Join Index With a Partitioned Primary Index

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