Manipulating Nonpartitioned NoPI Table Rows - Teradata VantageCloud Lake

For these cases with a primary-indexed target table, Database Engine 20 reads the rows in the nonpartitioned NoPI source table and then redistributes the rows as if 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 NoPI Tables and SELECT Statements for details of the limitations of these statements when 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 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 that index 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.

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 Database Engine 20 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, triggers, or referential integrity constraints 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.