15.00 - Manipulating Nonpartitioned NoPI Table Rows - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Manipulating Nonpartitioned NoPI Table Rows

After a nonpartitioned NoPI staging table has been populated with rows, you should execute one of the following types of DML request to move the nonpartitioned NoPI staging table source rows to a primary‑indexed or column-partitioned 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, 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 (see “Row Allocation for Teradata Parallel Data Pump” on page 237 and “Row Allocation for Primary‑Indexed Tables” on page 235). For a column-partitioned 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 nonpartitioned NoPI table rows prior to moving them to their target table.

  • See SQL Data Manipulation Language 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, there can be no single-AMP primary index access to table rows. However, you can create both unique and nonunique secondary indexes (see Chapter 10: “Secondary Indexes”) and join indexes (see Chapter 11: “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 or NoPI 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




    Column‑partitioned tables and join indexes

    “Column‑Partitioned Tables and Join Indexes” on page 285

    Using both NoPI and column‑partitioned tables

    “CREATE TABLE” in SQL Data Definition Language Detailed Topics

    NoPI tables

    Teradata Database Orange Book 541‑0007565B02 No Primary Index (NoPI) Table User’s Guide by Tam Ly

    Column‑partitioned tables and join indexes

    Teradata Database Orange Book 541-0009036A02 Teradata Columnar by Paul Sinclair and Carrie Ballinger