15.00 - NoPI Tables, Column-Partitioned Tables, and Column-Partitioned Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes

A NoPI object is a table or join index that does not have a primary index and always has a table kind of MULTISET.

The basic types of NoPI objects are:

  • Nonpartitioned NoPI tables
  • Column‑partitioned tables and join indexes (these may also have row partitioning)
  • The chief purpose of 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 a 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 for the rows of a NoPI table, its rows are not hashed to an AMP based on their primary index value. Instead, Teradata Database either hashes on the Query ID for a row (see “Row Allocation for Teradata Parallel Data Pump” on page 237), or it uses a different algorithm to assign the row to its home AMP (see “Row Allocation for FastLoad Operations Into Nonpartitioned NoPI Tables” on page 238).

    Teradata Database then generates a RowID for each row in a NoPI table by using a hash bucket that an AMP owns (see “Rowhash Value and RowID for NoPI Tables” on page 198 and “Row Allocation for Teradata Parallel Data Pump” on page 237). This strategy makes fallback and index maintenance very similar to their maintenance on a PI table.

    Global temporary tables, volatile tables, and temporal tables can be defined as NoPI tables. Column‑partitioned tables and column‑partitioned join indexes must be defined without a primary index. See “Column‑Partitioned Tables and Join Indexes” on page 285 for details about column partitioning and NoPI tables and join indexes.