15.00 - Effects on Tables with No Primary Index - Teradata Database

Teradata Database Support Utilities

Teradata Database

Effects on Tables with No Primary Index

There are special considerations when reconfiguring a system that includes NoPI tables (tables without primary indexes). NoPI tables include any table or join index that is column partitioned.

Reconfiguring a system that includes NoPI tables can result in data skew for those tables. When NoPI tables are initially loaded, their rows are distributed amongst the system AMPs in a random fashion, rather than having the distribution based on primary index hash values. Because the number of row hash values in a NoPI table is usually small, when a reconfiguration involves changes in the number of AMPs, some AMPs may end up with substantially more NoPI rows than other AMPs. Consequently, it is best to minimize the number of NoPI tables on the system before performing a reconfiguration, especially in cases where NoPI staging tables are large. For more information on NoPI tables, see Database Design and SQL Data Definition Language.

A NoPI table is marked as “Reconfig-Aborted” if Reconfiguration determines that the NoPI table rows must be redistributed according to rowhash values. Reconfiguration operations that may lead to a NoPI table being marked “Reconfig-Aborted” are:

  • Changing hash bucket size for a system
  • Performing a DELETE AMP operation immediately followed by an ADD AMP operation without re-inserting the NoPI rows into a new NoPI table before the ADD AMP operation.
  • Redistributing rows of a NoPI table can result in table skew, when a single AMP receives the majority of the rows. In those cases, permanent space can become an issue on that AMP. In order to access a NoPI table that is marked “Reconfig-Aborted”, create a new NoPI table, and use an INSERT...SELECT operation to copy the rows from the original NoPI table to the new table.

    Note: Permanent space in the database is divided equally among the AMPs of the system. If the presence of NoPI tables on a reconfigured system causes appreciable data skew, permanent space for the database may need to be increased to account for the AMPs that will need to store more data.

    For more information on NoPI tables, see SQL Data Definition Language and Database Design.