15.00 - INSERT... SELECT into CP and NoPI Tables - Teradata Database

Teradata Database Design

Teradata Database
User Guide

INSERT... SELECT into CP and NoPI Tables

When the target table of an INSERT … SELECT request is a column‑partitioned or 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 file. This is very efficient because it avoids a redistribution and sort. However, if the source table or the resulting spool is skewed, the target column‑partitioned or NoPI 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 table, also consider specifying a LOCAL ORDER BY clause with the INSERT … SELECT request to improve the effectiveness of autocompression.