A NoPI object is a table or join index that does not have a primary index or a primary AMP index and has a table kind of MULTISET.
- Nonpartitioned NoPI tables
- Column-partitioned NoPI tables and NoPI join indexes (these may also have row partitioning)
The chief purpose of nonpartitioned NoPI tables is as staging or sandbox 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 a column-partitioned table from an external client is to use FastLoad to insert the rows into a staging table, and then use an INSERT ... SELECT request to load the rows from the staging table into the column-partitioned target table.
Because there is no primary index or 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, Database Engine 20 hashes on the Query ID for a row or uses a different algorithm to assign the row to its home AMP.
Database Engine 20 then generates a RowID for each row in a NoPI table by using a hash bucket that an AMP owns. This strategy makes fallback and index maintenance 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 be defined without a primary index but can also be defined with a primary index or a primary AMP index. See Column Partitioning for details about column partitioning and NoPI tables and join indexes.
Related Information
Orange Books are located on https://docs.teradata.com/. You must sign in to access the Orange Books.
| Topic | Reference |
|---|---|
| Column-partitioned tables and join indexes | Column Partitioning |
| Using both NoPI and column-partitioned tables | CREATE TABLE (Index Definition Clause) |
| NoPI tables | No Primary Index (NoPI) Table User Guide Orange Book, 541-0007565 |
| Column-partitioned tables and join indexes | Teradata Columnar Orange Book, 541-0009036 |