15.00 - Row Allocation for Primary-Indexed Tables - Teradata Database

Teradata Database Design

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

Row Allocation 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

primary 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 Allocation for Teradata Parallel Data Pump” on page 237).

4  

 

IF the row has a…

THEN …

a primary 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. Once the row reaches its AMP, the AMP software generates a different hash bucket number for it that is stored in its rowID.

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 partition number, if it exists, and the hash code to determine where the row should be stored. The partition number provides the higher-order grouping attribute.

 

IF the primary index is this type …

THEN AMP software …

Nonpartitioned

Assigns the next uniqueness value. When available unique values are exhausted, the next hash bucket owned by this AMP is substituted.

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 674 for information about how nulls are interpreted by commercially available relational database management systems.

Partitioned

1 Uses the partitioning expressions for the PPI to calculate the internal partition number for the row.

2 The internal partition number determines the row partition to which the row is assigned.

3 Assigns the next uniqueness value.

4 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 Allocation” on page 242 for more information and for information on PPIs.

The row is then stored in the table in rowID order.