Row Assignment for NoPI Tables - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Row Assignment for NoPI Tables

Because NoPI tables have no primary index, Teradata Database must build their rowhash value differently than it does for primary‑indexed rows, generating a rowhash value in such a way that for a new request, data is usually sent to a different AMP than the AMP the previous request distributed its rows to. This method attempts to balance the distribution of rows across the AMPs as much as possible. The hash of the Query ID for the request is used for this purpose because the QueryIDn uses the PE vproc ID in its high digits and a counter-based value in its low digits, which supports both single‑session and multiple-sessions INSERT operations equally well.

To maximize efficiency, array inserts (for example, those done by Teradata Parallel Data Pump) on a NoPI table always pack as many rows as there are in the request from the client into the same AMP step. The rows are sent to the same AMP in one insert step. Teradata Database uses hashing to distribute the rows that are to be inserted into a NoPI table and determine the AMP destination for each insert step. The hashing algorithm spreads the rows across the AMPs in a way that avoids skewing.

Refer to the following graphic for an illustration of the row assignment process for single-row and array inserts into a NoPI table.

There are two cases for a simple insert operation.

  • Only a single data row is processed.
  • Multiple data rows are processed for an array INSERT request.
  • For both cases, the system copies the generated rowhash value based on the Query ID into the rowID of each row being processed as soon as it has been built. This rowhash value is generated once per request so that in the case of an array insert, the same rowhash value is used for all of the rows in the request. That means that Teradata Database sends all rows for that request to the same AMP, possibly in the same step.

    The source spool for rows that are inserted into NoPI tables by INSERT … SELECT operations are, by default, appended AMP‑locally to their target table, so there is no need to hash them to a destination AMP.

    You should consider the following recommendations for Teradata Parallel Data Pump array inserts into nonpartitioned tables.

  • If the order of data application is not important, set the SERIALIZE option to OFF.
  • Do not set the number of sessions for a Teradata Parallel Data Pump job to a larger value than the number of AMPs on the system.