Inserting Into NoPI Tables
You can use INSERT to insert rows into NoPI tables. Teradata Parallel Data Pump operations use Array INSERT on NoPI tables to load data. You can also perform batch inserts of data into NoPI tables using INSERT … SELECT operations. Inserts are transient journaled in the same way they are for primary indexed tables. For more information about NoPI tables, see Database Design.
The following INSERT examples are based on these table definitions:
CREATE TABLE sales,
FALLBACK (
ItemNbr INTEGER NOT NULL,
SaleDate DATE FORMAT 'MM/DD/YYYY' NOT NULL,
ItemCount INTEGER)
PRIMARY INDEX (ItemNbr);
CREATE TABLE newsales,
FALLBACK (
ItemNbr INTEGER NOT NULL,
SaleDate DATE FORMAT 'MM/DD/YYYY' NOT NULL,
ItemCount INTEGER)
NO PRIMARY INDEX;
For INSERT requests, the system randomly selects an AMP to send the row or rows. This is true for simple requests like the one below, and more complex requests that involve the insertion of multiple arrays of rows.
INSERT INTO newsales (100, '11/01/2007', 10);
The AMP then converts the row or array of rows into the proper internal format and appends them to the end of the target table (newsales in this example).
When inserting data from a source table into a NoPI target table using an INSERT … SELECT request like the one below, data from the source table is not redistributed. The data is locally appended to the target table.
If the SELECT is constrained from a source table, and the rows returned from the AMPs are skewed, the NoPI or NoPI column‑partitioned table can become skewed, because the set of rows is locally inserted into the table without redistribution.
INSERT INTO newsales
SELECT *
FROM sales;