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
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
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.
UNIQUE column constraints
PRIMARY KEY and FOREIGN KEY constraints
BLOB, CLOB, ARRAY, VARRAY, UDT, Period, Geospatial, and row‑level security constraint
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
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).