Row Assignment for Primary‑Indexed Tables
The process for assigning a particular primary‑indexed table row to an AMP is depicted in the following graphic. The logical process involved in this assignment is described in the table following the graphic.
The logical process involved in row assignment for a table SQL insert operation is roughly the following.
1 The INSERT request passes its syntactical, lexical, and security constraints.
2 A row header is prepended to each row that includes information about whether the table into which it is to be inserted is partitioned.
3
|
IF the row has … |
THEN the … |
|
a primary index or primary AMP index |
primary index or primary AMP index is submitted to the hashing algorithm for processing. |
|
no primary index |
Query ID for the request is submitted to the hashing algorithm for processing (see “Row Assignment for NoPI Tables” on page 185). |
4
|
IF the row has a… |
THEN … |
|
a primary index or primary AMP index |
the hashing algorithm produces a rowhash value. Part of that value (the high-order 16 or 20 bits, depending on a system setting) is used as the hash bucket number. |
|
no primary index |
the hashing algorithm produces a rowhash value consisting of the hash bucket number and a remainder. Teradata Database uses this value only to assign a row to an AMP. |
5 The value of the hash bucket number is checked against the hash map and the destination AMP for the row is determined.
6 The Dispatcher sends the row set across the BYNET to its destination AMP.
7 Check uniqueness. If a unique primary or secondary index exists, it will be used to ensure uniqueness. If neither exists and no UNIQUE specifier exists on a column (which implicitly creates a unique primary or secondary index), and the table is a SET table, then check for uniqueness by scanning the rows having the same hash value.
8 The AMP uses both the internal partition number, if it exists, and the rowhash value to determine where the row should be stored. The internal partition number provides the higher-order grouping attribute.
|
IF the primary index is this type … |
THEN AMP software … |
|||
|
Nonpartitioned |
Assigns the next uniqueness value for the rowhash value (or 1 if there is not an existing row with this rowhash value for this AMP). When Teradata Database does duplicate row checks, it considers nulls to be equal, while in SQL conditions, null comparisons evaluate to UNKNOWN. See “Inconsistencies in How SQL Treats Nulls” on page 618 for information about how nulls are interpreted by commercially available relational database management systems. |
|||
|
Partitioned |
1 Uses the partitioning expressions to calculate the internal partition number for the row. 2 Assigns the next uniqueness value for this internal partition number and rowhash value (or 1 if there is not an existing row with this internal partition number and rowhash value for this AMP). 3 Go to Stage 9. |
|||
9 The file system assigns the row to the appropriate data block and stores it in order of its internal partition number, hash value, and uniqueness value.
See “Row‑Partitioned Row Assignment” on page 190 for more information and for information on PPIs.
The row is then stored in the table in rowID order.