Teradata NoPI Tables

Teradata NoPI Tables

Teradata No Primary Index (NoPI) table are the tables for which primary index is not defined.

• Characteristics for NoPI tables
• Types of NoPI tables
• When NoPI tables should be used
• How NoPI Table works
• Exceptions when NoPI tables will become skewed
• Objects/properties that can be defined for NoPI tables
• Limitations of NoPI tables
• Advantage for Fastload when loading a NoPI table

➠ Characteristics for NoPI tables :

• Even though no hashing algorithm is used for NoPI tables, Teradata generally ensures that the rows are distributed randomly and evenly among all the AMPs.
• NoPI table cannot be created as SET table and it will throw below error when attempting to do so.

  
  *** Failure 3706 Syntax error: Cannot create a NoPI table as a SET table.
  

➠ There are 2 types of NoPI tables

1. Non-partitioned NoPI tables

   
   Syntax/Example:
   
   CREATE MULTISET TABLE tutorial_db.tablename
   (                                   
     col_1   INTEGER,
     col_2   VARCHAR(50) TITLE 'Column Name 2'
   )
   NO PRIMARY INDEX; 
   

   
   
2. Column‑partitioned NoPI tables

   
   Syntax/Example:
   
   CREATE MULTISET TABLE tutorial_db.tablename
   (                                   
     col_1   INTEGER,
     col_2   VARCHAR(50) TITLE 'Column Name 2'
   )
   NO PRIMARY INDEX
   Partition By Column(col_1,col2); 
   

➠ NoPI tables should be used only for the following purpose

• Temporary/Staging purpose
• Column-partitioning

➠ How No Primary Index Tables works

• Teradata does not use hashing algorithm for NoPI tables to distribute data.
• Teradata ensures that all rows are distributed randomly and evenly among all the AMPs.
• Like primary index tables, ROWID is generated for each row as every row must be uniquely identifiable in teradata.
• The process to generate ROWID of NoPI table is different than the ROWID of a primary index table.
  
• ROWID for NoPI table is calculated as hashbucket of AMP plus uniqueness value.

  
  ROWID = HASHBUCKET of AMP + Uniqueness value
  

  • Uniqueness : A uniqueness value, if the uniqueness value overflows then it resets to 1.
  • Hash Bucket of AMP : Lowest numbered hash bucket for this AMP from the NoPI hash map. This value increases to next hash bucket if uniqueness value overflows. Teradata does not compute hash bucket value by hashing values of any columns but uses lowest number hash bucket from the NoPI hash map for the AMP where the row is randomly sent initially.

➠ Exceptions when NoPI tables will become skewed

1. INSERT-SELECT statement from a skewed primary index table into an NoPI table. In this case, all rows will stay on their current AMP and as current table is skewed then NoPI table will also end up as skewed NoPI table. This skewness can be prevented by using HASH BY [column] clause or HASH BY RANDOM clause that provides good distribution to redistribute the data.
2. Copy NoPI tables data from one source system and restore those NoPI tables on different target system(with lesser or more AMPs) will result in table being skewed.

➠ Objects & table properties can be used for NoPI tables

• Fallback
• Secondary Index
• Join Index
• Primary Key, Foreign Key, Unique column and CHECK constraints
• XML and LOB columns

➠ Limitation of NoPI tables

• TPT Loader and Fastload utility generally discard duplicate rows for SET and MULTISET tables with primary indexes but duplicate rows are not discarded by Teradata for NoPI tables.
• FastLoad can be used to load data into an empty non-partitioned NoPI table but not to load data into a column‑partitioned NoPI table.
• MultiLoad utility cannot be used to load data into NoPI tables.
• SQL MERGE cannot be used to update/insert into a NoPI target table but SQL MERGE can update/insert into primary indexed target table from a NoPI table. If user tries then command will fail with below error.

  
  *** Failure 9252 An invalid statement was attempted on a table without a primary index: Merge-Into NoPI tables is not allowed. 
  

• SQL Upsert cannot be used to update/insert data into a NoPI target table. If user tries then command will fail with below error.

  
  *** Failure 5565 The UPDATE specified in the UPSERT statement is a complex update.
  

• Hash indexes cannot be defined on NoPI or column‑partitioned NoPI tables.
• Identity column cannot be defined in Non-partitioned NoPI tables (supported for column‑partitioned NoPI tables). If user tries to define then ddl command will fail with below error.

  
  *** Failure 3706 Syntax error: Identity Column in a NoPI table is unsupported.
  

• Permanent journal cannot be defined for NoPI tables.
  

➠ Advantage for Fastload when loading a NoPI table

Lets look at the phases of the Fastload for a primary index table
• Phase 1: All incoming rows are randomly distributed to all AMPs.
• Phase 2: Rows are hashed through the primary index value and forwarded to the appropriate AMPs. And the appropriate AMPs will sort the received rows by ROWID.

The Fastload for NoPI tables ends after phase 1 as soon as the rows have been randomly distributed to the AMPs. There is no hashing, no sorting and no redistribution of rows.

Another advantage of NoPI tables is that the rows are appended to the end of the data blocks of the respective table. This avoids any overhead that is normally caused by sorting the rows by row hash into the data blocks.