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

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

Teradata 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 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, Teradata Database 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 Teradata Database 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, Teradata Database 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 Teradata Database 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

Manipulating Nonpartitioned NoPI Table Rows

For these cases with a primary-indexed target table, Teradata Database 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.

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 Teradata Database 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 Topics