INSERT... SELECT into NoPI and Column-Partitioned NoPI Tables - Teradata Database

Teradata Database Design

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

INSERT... SELECT into NoPI and Column-Partitioned NoPI Tables

When the target table of an INSERT … SELECT request is a NoPI table, Teradata Database inserts the data from the source table locally into the target table, whether it comes directly from the source table or from an intermediate spool. This is very efficient because it avoids a redistribution and sort. However, if the source table or the resulting spool is skewed, the target table can also be skewed. In this case, you can specify a HASH BY clause to redistribute the data from the source before Teradata Database executes the local copy operation.

Consider using hash expressions that provide good distribution and, if appropriate, improve the effectiveness of autocompression for the insertion of rows into the target table. Alternatively, you can specify HASH BY RANDOM to achieve good distribution if there is not a clear choice for the expressions to hash on.

When inserting into a column‑partitioned NoPI table, also consider specifying a LOCAL ORDER BY clause with the INSERT … SELECT request to improve the effectiveness of autocompression.