Loading No Primary Index Tables
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.
Note: 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 SQL Data Definition Language (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.