Rules and Limitations for NoPI and Column-Partitioned Tables - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Rules and Limitations for NoPI and Column‑Partitioned Tables

The rules and limitations for NoPI tables and any type of column-partitioned table are the same as those for primary‑indexed tables with the following exceptions:

  • You cannot create a nonpartitioned NoPI join index.
  • You can create a column‑partitioned join index (with or without row partitioning).

  • You cannot create a NoPI or column-partitioned:
  • Queue table
  • Error table
  • SET table
  • The default table type for NoPI and column‑partitioned tables in both Teradata and ANSI/ISO session modes is always MULTISET.

  • Global temporary trace tables
  • Global temporary trace tables do not have a primary index or a primary AMP index by default; however, you are not allowed to specify the NO PRIMARY INDEX option when you create a global temporary table.

  • If none of the clauses PRIMARY INDEX (column_list), PRIMARY AMP INDEX, NO PRIMARY INDEX, or PARTITION BY are specified explicitly in a CREATE TABLE or CREATE JOIN INDEX request, whether the table or join index is created with or without a primary index or primary AMP index generally 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 (see “Primary Index Defaults” on page 211 and Utilities for details and exceptions).
  • Neither NoPI tables nor column‑partitioned tables can specify a permanent journal.
  • Nonpartitioned NoPI tables cannot specify an identity column.
  • Column‑partitioned tables can specify an identity column.

  • Hash indexes cannot be defined on NoPI or column‑partitioned tables.
  • SQL UPDATE (Upsert Form) requests cannot update either a NoPI or a column‑partitioned target table.
  • SQL MERGE requests cannot update or insert into either a NoPI or a column‑partitioned target table.
  • SQL MERGE requests can update or insert into a primary‑indexed target table from a NoPI or column‑partitioned source table.

  • You cannot load rows into either a nonpartitioned NoPI or a column‑partitioned table using the MultiLoad utility.
  • You can define all of the following commonly used features for both NoPI and column‑partitioned tables:

  • Fallback
  • Secondary indexes
  • Join indexes
  • PRIMARY KEY and UNIQUE column constraints
  • CHECK constraints
  • FOREIGN KEY constraints
  • Triggers
  • XML, BLOB, and CLOB columns.
  • Note: Because there is normally only one row hash value per AMP for NoPI tables, there is also a limit of approximately 256M rows per AMP for NoPI tables that contain columns typed as XML, BLOB, or CLOB.

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

  • Nonpartitioned base tables
  • Column‑partitioned base tables (with or without row partitioning)
  • Column‑partitioned, single‑table, non‑aggregate, noncompressed join indexes (with or without row partitioning)
  • Nonpartitioned global temporary tables
  • Nonpartitioned volatile tables