16.20 - Nonpartitioned NoPI Tables - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
Tables that do not have a primary index and are not column-partitioned, referred to as nonpartitioned NoPI tables, should generally be used for only 2 reasons.
  • As staging tables for FastLoad and Teradata Parallel Data Pump array INSERT load operations. For more information, see Teradata Vantage™ - Database Design, B035-1094.

    After the data is loaded into these tables, you can use INSERT … SELECT, MERGE, or UPDATE … FROM to copy the rows to their destination primary-indexed tables. INSERT … SELECT and UPDATE statements can also be used to copy rows from a primary-indexed source table into a NoPI target table, while MERGE statements 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.
  • Nonpartitioned NoPI tables, which the current topic describes.
  • Column-partitioned tables, which are described in Column-Partitioned Tables.
You can use the following SQL DML statements to manipulate nonpartitioned NoPI table data.
  • DELETE
  • INSERT
  • SELECT
  • UPDATE
Nonpartitioned NoPI tables have the following restrictions.
  • You cannot create a nonpartitioned NoPI table as a SET table.

    The unalterable default table type for nonpartitioned NoPI tables in all session modes is MULTISET.

  • You cannot specify a column name list following the NO PRIMARY INDEX specification.
  • If you do not specify PRIMARY INDEX (column_list) or NO PRIMARY INDEX in your CREATE TABLE statement, then whether the table is created with 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 Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - Database Utilities , B035-1102 .

    The default setting for PrimaryIndexDefault is D. 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 a nonpartitioned 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.
  • Nonpartitioned NoPI tables cannot have a permanent journal.
  • Nonpartitioned NoPI tables cannot have an identity column.
  • Hash indexes cannot be specified on nonpartitioned NoPI tables because hash indexes inherit the primary index of their underlying base table, and nonpartitioned NoPI tables have no primary index.
  • SQL MERGE statements cannot update or insert into either nonpartitioned NoPI tables or column-partitioned target tables.

    SQL MERGE statements can update or insert into a primary-indexed target table from either a nonpartitioned or a column-partitioned source table.

  • You cannot load rows into a nonpartitioned NoPI table or any normalized table using the MultiLoad utility.
    You can load rows into a nonpartitioned NoPI table using the FastLoad utility with the exception of normalized NoPI tables, Teradata Parallel Data Pump array INSERT operations, and INSERT … SELECT statements.
You can define all of the following features for nonpartitioned 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.
    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 nonpartitioned NoPI tables, there is also a limit of approximately 64K rows per AMP for nonpartitioned NoPI tables that contain columns typed as BLOBs or CLOBs.
You can define any of the following table types as nonpartitioned NoPI tables.
  • Nonpartitioned base data tables
  • Global temporary tables
  • Volatile tables

You cannot define any of the following table types as an nonpartitioned NoPI table.