NoPI Tables and Indexes | Database Design | VantageCloud Lake - NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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.

The basic types of NoPI objects are:
  • 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.

You can also use Teradata Parallel Data Pump array INSERT operations to load rows into a column-partitioned table.
Array INSERT operations are only supported on the Block File System on the primary cluster. They are not available for the Object File System.

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, Analytics Database hashes on the Query ID for a row or uses a different algorithm to assign the row to its home AMP.

Analytics Database 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