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 :



➠ 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



➠ How No Primary Index Tables works



➠ 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



➠ Limitation of NoPI tables



➠ Advantage for Fastload when loading a NoPI table

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.