NoPI Tables, Column‑Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes
A NoPI object is a table or join index that does not have a primary index or a primary AMP index and always has a table kind of MULTISET.
The basic types of NoPI objects are:
The chief purpose of nonpartitioned NoPI tables is as staging tables. FastLoad can efficiently load data into empty nonpartitioned NoPI staging tables because NoPI tables do not have the overhead of row distribution among the AMPs and sorting the rows on the AMPs by rowhash.
Nonpartitioned NoPI tables are also critical to support Extended MultiLoad Protocol (MLOADX). A nonpartitioned NoPI staging table is used for each MLOADX job.
The optimal method of loading rows into any type of column‑partitioned table from an external client is to use FastLoad to insert the rows into a staging table, then use an INSERT … SELECT request to load the rows from the source staging table into the column‑partitioned target table.
You can also use Teradata Parallel Data Pump array INSERT operations to load rows into a column‑partitioned table.
Global temporary trace tables are, strictly speaking, also a type of NoPI table because they do not have a primary index, though they are generally not treated as NoPI tables.
Because there is no primary index or a primary AMP index for the rows of a NoPI table, its rows are not hashed to an AMP based on their primary index or a primary AMP index value. Instead, Teradata Database either hashes on the Query ID for a row (see “Row Assignment for NoPI Tables” on page 185), or it uses a different algorithm to assign the row to its home AMP (see “Row Assignment for FastLoad Operations Into Nonpartitioned NoPI Tables” on page 186).
Teradata Database then generates a RowID for each row in a NoPI table by using a hash bucket that an AMP owns (see “Row Distribution for NoPI Tables” on page 148 and “Row Assignment for NoPI Tables” on page 185). This strategy makes fallback and index maintenance very similar to their maintenance on a PI table.
Global temporary tables and volatile tables can be defined as nonpartitioned NoPI tables but not as partitioned NoPI tables. Column‑partitioned tables and column‑partitioned join indexes can also be defined without a primary index but can have a primary index or a primary AMP index. See “Column‑Partitioned NoPI Tables and Join Indexes” on page 235 for details about column partitioning and NoPI tables and join indexes.