15.00 - Unpartitioned NoPI Tables - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Unpartitioned NoPI Tables

Tables that do not have a primary index and are not column‑partitioned, referred to as unpartitioned NoPI tables, should generally be used for only 2 reasons.

  • As staging tables for FastLoad and Teradata Parallel Data Pump array INSERT load operations (see Database Design for details).
  • Once the data has been loaded into these tables, you can use SQL requests such as INSERT … SELECT, MERGE, or UPDATE … FROM to copy the rows to their destination primary‑indexed tables. INSERT … SELECT and UPDATE requests can also be used to copy rows from a primary‑indexed source table into a NoPI target table, while MERGE requests cannot.

  • As temporary holding, or sandbox, tables when an appropriate primary index has not yet been defined for the table they will eventually populate.
  • There are 2 types of NoPI tables.

  • Unpartitioned NoPI tables, which the current topic describes.
  • Column‑partitioned tables, which are described in “Column‑Partitioned Tables” on page 577.
  • You can use the following SQL DML statements to manipulate unpartitioned NoPI table data.

  • Unpartitioned NoPI tables have the following restrictions.

  • You cannot create an unpartitioned NoPI table as a SET table.
  • The unalterable default table type for unpartitioned NoPI tables in all session modes is MULTISET.

  • You cannot specify a column name list following the NO PRIMARY INDEX specification.
  • If you neither specify PRIMARY INDEX (column_list) nor NO PRIMARY INDEX explicitly in your CREATE TABLE request, then whether the table is created with or without a primary index depends on whether a PRIMARY KEY or UNIQUE constraint is specified for any of the columns and on the setting of the DBS Control parameter PrimaryIndexDefault (for details and exceptions, see Database Design and Utilities: Volume 1 (A-K)).
  • The default setting for PrimaryIndexDefault is D, which means that if you do not specify either an explicit PRIMARY INDEX or NO PRIMARY INDEX option, Teradata Database creates a UPI on a declared PRIMARY KEY, and if none exists, on the first column defined with a UNIQUE attribute. If none is found, Teradata Database creates the table with a NUPI on the first index‑eligible column that is defined.

  • You cannot specify partitioning of the primary index for an unpartitioned NoPI table because it cannot have a primary index to partition; however, you can define a NoPI table to have column‑partitioning (see “Column‑Partitioned Tables” on page 577).
  • Unpartitioned NoPI tables cannot have a permanent journal.
  • Unpartitioned NoPI tables cannot have an identity column.
  • Hash indexes cannot be specified on unpartitioned NoPI tables because hash indexes inherit the primary index of their underlying base table, and unpartitioned NoPI tables have no primary index.
  • SQL MERGE requests cannot update or insert into either unpartitioned NoPI tables or column‑partitioned target tables.
  • SQL MERGE requests can update or insert into a primary‑indexed target table from either an unpartitioned or a column‑partitioned source table.

  • You cannot load rows into an unpartitioned NoPI table or any normalized table using the MultiLoad utility.
  • Note: You can load rows into an unpartitioned NoPI table using the FastLoad utility with the exception of normalized NoPI tables, Teradata Parallel Data Pump array INSERT operations, and INSERT … SELECT requests.

    You can define all of the following features for unpartitioned NoPI tables.

  • TRANSACTIONTIME columns, VALIDTIME columns, or both.
  • Fallback
  • Secondary indexes
  • Join indexes
  • UNIQUE column constraints
  • CHECK constraints
  • PRIMARY KEY and FOREIGN KEY constraints
  • Triggers
  • BLOB, CLOB, ARRAY, VARRAY, UDT, Period, Geospatial, and row‑level security constraint columns.
  • Note: There is a limit of approximately 64K rows per row hash value for LOBs. Because there is normally only 1 row hash value per AMP for unpartitioned NoPI tables, there is also a limit of approximately 64K rows per AMP for unpartitioned NoPI tables that contain columns typed as BLOBs or CLOBs.

    You can define any of the following table types as unpartitioned NoPI tables.

  • Unpartitioned base data tables
  • Global temporary tables
  • Volatile tables
  • You cannot define any of the following table types as an unpartitioned NoPI table.

  • Queue tables (see “CREATE TABLE (Queue Table Form)” on page 673).
  • Error tables (see “CREATE ERROR TABLE” on page 228).