For better performance when bulk loading data using Teradata FastLoad or through SQL sessions (in particular, using the INSERT statement from TPump with the ArraySupport option enabled), you can create a No Primary Index (NoPI) table to use as a staging table to load your data. Without a primary index (PI), the system can store rows on any AMP that is desired, appending the rows to the end of the table.
By avoiding the data redistribution normally associated with loading data into staging tables that have a PI, NoPI tables provide a performance benefit to applications that load data into a staging table, transform or standardize the data, and then store the converted data into another staging table.
Applications can also benefit by using NoPI tables in the following ways:
- As a log file
- As a sandbox table to store data until an appropriate indexing method is determined
A query that accesses the data in a NoPI table results in a full-table scan unless you define a secondary index on the NoPI table and use the columns that are indexed in the query.