NoPI Tables and Indexes | Database Design | Teradata Vantage - 17.10 - NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

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:
  • 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 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.

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

Vantage 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 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 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.

INSERT ... SELECT into NoPI Tables

When the target table of an INSERT ... SELECT request is a NoPI table, Vantage inserts the data from the source table locally into the target table, whether it comes directly from the source table or from an intermediate spool. This is very efficient because it avoids a redistribution and sort. However, if the source table or the resulting spool is skewed, the target table can also be skewed. In this case, you can specify a HASH BY clause to redistribute the data from the source before Vantage executes the local copy operation.

Consider using hash expressions that provide good distribution and, if appropriate, improve the effectiveness of autocompression for the insertion of rows into the target table. Alternatively, you can specify HASH BY RANDOM to achieve good distribution if there is not a clear choice for the expressions to hash on.

When inserting into a column-partitioned NoPI table, also consider specifying a LOCAL ORDER BY clause with the INSERT ... SELECT request to improve the effectiveness of autocompression.

Uses for Nonpartitioned NoPI Tables

Nonpartitioned NoPI tables are particularly useful as staging tables for bulk data loads. When a table has no primary index or a primary AMP index, its rows can be dispatched to any given AMP arbitrarily and the rows do not need to be sorted, so the system can load data into a staging table faster and more efficiently using FastLoad or Teradata Parallel Data Pump array INSERT operations. You can only use FastLoad to load rows into a NoPI table when it is unpopulated, not partitioned, and there are no USIs.

You must use Teradata Parallel Data Pump array INSERT operations to load rows into NoPI tables that are already populated. If a NoPI table is defined with a USI, Vantage checks for an already existing row with the same value for the USI column (to prevent duplicate rows) when you use Teradata Parallel Data Pump array INSERT operations to insert rows into it.

By storing bulk loaded rows on any arbitrary AMP, the performance impact for both CPU and I/O is reduced significantly. After having been received by Vantage, all of the rows can be appended to a nonpartitioned or column-partitioned NoPI table without needing to be redistributed to their hash-owning AMPs.

Because there is no requirement for such tables to maintain their rows in any particular order, the system need not sort them. The performance advantage realized from NoPI tables is achieved optimally for applications that load data into a staging table, which must first undergo a conversion to some other form, and then be redistributed before they are stored in a secondary staging table or the target table.

Using a nonpartitioned NoPI table as a staging table for such applications avoids the row redistribution and sorting required for primary-indexed staging tables. Another advantage of nonpartitioned NoPI tables is that you can quickly load data into them and be finished with the acquisition phase of the utility operation, which frees client resources for other applications.

Both NoPI and column-partitioned NoPI tables are also useful as so-called sandbox tables when an appropriate primary index has not yet been defined for the primary-indexed table they will eventually populate. This use of a NoPI table enables you to experiment with several different primary index possibilities before deciding on the most optimal choice for your particular application workloads.

Rules and Limitations for NoPI and Column-Partitioned Tables

The rules and limitations for NoPI tables and any type of column-partitioned table are the same as those for primary-indexed tables with the following exceptions:
  • You cannot create a nonpartitioned NoPI join index.

    You can create a column-partitioned join index (with or without row partitioning).

  • You cannot create a NoPI or column-partitioned:
    • Queue table
    • Error table
    • SET table

      The default table type for NoPI and column-partitioned tables in both Teradata and ANSI/ISO session modes is always MULTISET.

    • Global temporary trace tables

      Global temporary trace tables do not have a primary index or a primary AMP index by default; however, you are not allowed to specify the NO PRIMARY INDEX option when you create a global temporary table.

  • If none of the clauses PRIMARY INDEX (column_list), PRIMARY AMP INDEX, NO PRIMARY INDEX, or PARTITION BY are specified explicitly in a CREATE TABLE or CREATE JOIN INDEX request, whether the table or join index is created with or without a primary index or primary AMP index generally depends on whether a PRIMARY KEY or UNIQUE constraint is specified for any of the columns and on the setting of the DBS Control field PrimaryIndexDefault (see Primary Index Defaults and Teradata Vantage™ - Database Utilities, B035-1102 for details and exceptions).
  • Neither NoPI tables nor column-partitioned tables can specify a permanent journal.
  • Nonpartitioned NoPI tables cannot specify an identity column.

    Column-partitioned tables can specify an identity column.

  • Hash indexes cannot be defined on NoPI or column-partitioned tables.
  • SQL UPDATE (Upsert Form) requests cannot update either a NoPI or a column-partitioned target table.
  • SQL MERGE requests cannot update or insert into either a NoPI or a column-partitioned target table.
  • You cannot load rows into either a nonpartitioned NoPI or a column-partitioned table using the MultiLoad utility.
You can define all of the following commonly used features for both NoPI and column-partitioned tables:
  • Fallback
  • Secondary indexes
  • Join indexes
  • PRIMARY KEY and UNIQUE column constraints
  • CHECK constraints
  • FOREIGN KEY constraints
  • Triggers
  • XML, BLOB, and CLOB columns.
    Because there is normally only one row hash value per AMP for NoPI tables, there is also a limit of approximately 256M rows per AMP for NoPI tables that contain columns typed as XML, BLOB, or CLOB.
You can define any of the following table types as NoPI tables:
  • Nonpartitioned base tables
  • Column-partitioned base tables (with or without row partitioning)
  • Column-partitioned, single-table, non-aggregate, noncompressed join indexes (with or without row partitioning)
  • Nonpartitioned global temporary tables
  • Nonpartitioned volatile tables

Manipulating Nonpartitioned NoPI Table Rows

After a nonpartitioned NoPI staging table has been populated with rows, you can execute one of the following types of DML request to move the nonpartitioned NoPI staging table source rows to a target table.
  • MERGE (for a primary-indexed target table only)
  • UPDATE FROM (for a primary-indexed target table only)

For these cases with a primary-indexed target table, Vantage reads the rows in the nonpartitioned NoPI source table and then redistributes them in the same way it redistributes them from a primary-indexed table to their hash-owning AMPs based on the primary index of the target table. For a NoPI target table, the rows are locally copied, unless the INSERT ... SELECT specifies a HASH BY clause.

You can use the following DML statements to manipulate or retrieve NoPI table rows prior to moving them to their target table.

See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for details of the limitations of these statements when they are used with NoPI tables.

You cannot use UPDATE (Upsert Form) requests to change data in either a NoPI or a column-partitioned table.

Without a primary index or a primary AMP index, there can be no single-AMP access to table rows. However, you can create both unique and nonunique secondary indexes (see Secondary Indexes) and join indexes (see Join and Hash Indexes) on NoPI tables, and with appropriate secondary indexes defined, you can specify those indexes in your query conditions in such a way as to avoid full-table scans. You cannot specify a join index in a query condition, but if an appropriate join index exists, the Optimizer can use it to create the access or join plan for a request.

You cannot use the MultiLoad utility to load rows into either NoPI or column-partitioned tables.

You can use FastLoad to load rows into an empty nonpartitioned NoPI table, but not to load rows into a column-partitioned table.

Keep in mind that secondary and join indexes can slow the loading of rows into a NoPI table using Teradata Parallel Data Load array INSERTs. FastLoad is more efficient than Teradata Parallel Data Pump for loading rows into an empty nonpartitioned NoPI table because Vantage processes each Teradata Parallel Data Pump request as a separate transaction.

If you use FastLoad to load rows into a nonpartitioned NoPI table, you cannot create any secondary or join indexes, CHECK constraints, referential integrity constraints, or triggers on the table until after the load operation has completed because FastLoad cannot load rows into a table defined with any of those features. You can use FastLoad to load rows into an empty nonpartitioned NoPI tables that is defined with row-level security constraints, however.

Related Information

Topic Reference
Column-partitioned tables and join indexes Column Partitioning
Using both NoPI and column-partitioned tables CREATE TABLE information in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184
NoPI tables No Primary Index (NoPI) Table User Guide Orange Book, 541-0007565
Column-partitioned tables and join indexes Teradata® Columnar Primer Orange Book, TDN0009884