- 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.
- Nonpartitioned NoPI tables, which the current topic describes.
- Column-partitioned tables, which are described in Column-Partitioned Tables.
- DELETE
- INSERT
- SELECT
- UPDATE
- 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, Vantage 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, Vantage 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.
- 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.
- Nonpartitioned base data tables
- Global temporary tables
- Volatile tables
- Queue tables (see CREATE TABLE (Queue Table Form)).
- Error tables (see CREATE ERROR TABLE).
You cannot define any of the following table types as an nonpartitioned NoPI table.