Loading No Primary Index Tables | Teradata FastLoad - 17.10 - Loading No Primary Index Tables - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
17.10
Published
June 2021
Last Update
2021-07-01
Content Type
Programming Reference
Publication ID
B035-2411-061K
Language
English (United States)

A NoPI Table is a table that has no primary index. These tables can be used as staging tables where data is always appended to the table, making population of the table generally faster than that of a traditional table containing a primary index.

FastLoad is generally faster than TD TPump whether the target table is PI or NoPI. TD TPump has a bigger performance improvement with NoPI but it is still slower than FastLoad.

The “table must be empty” restriction applies if the target table is a NoPI table. FastLoad cannot load into a populated NoPI table. See the No Primary Index discussion in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

FastLoad can load Primary Index (PI) tables defined as MULTISET, but all duplicate rows are discarded, the target table is treated as if it were a SET table. NoPI tables are inherently MULTISET, since no duplicate row checking is possible (duplicate rows can be on different AMPs). Therefore, when FastLoad targets a NoPI table, no check is made for duplicate rows, and duplicate rows are not discarded.