NOPI (No primary index) Table in Teradata

As the name suggests, No primary index table means there will be no primary index defined on Teradata table. This feature has been introduced in V2R13 release.  The main purpose of this feature which allows us to create tables with no primary index in Teradata is to improve the performance of FastLoad and Tpump  array insert data loading operations.

We can use this kind of table in staging area of Data warehouse.

Syntax for NOPI table – CREATE Table:

1. CREATE MULTISET TABLE teradatapoint
           (Column1 INTEGER NOT NULL,
            Column2 DATE FORMAT ‘MM/DD/YYYY’ NOT NULL,
            Column3 INTEGER)
   NO PRIMARY INDEX;

      2.  CREATE MULTISET TABLE teradatapoint_tmp AS
           (SELECT * FROM teradatapoint ) WITH DATA NO PRIMARY INDEX;

In this case table teradatapoint will act as a source table and it can be a PI table or NOPI table.

Data Distribution:-

As we know that there is no primary index involved in NOPI table, its rows are not hashed to AMP based on primary index value. Teradata database, either hashes on query id or use some different algorithm to assign the rows to its destination AMP.

Once the row reaches to the destination AMP, Teradata generates a ROWID for it by selecting a random hash bucket which is AMP local.

Unlike FastLoad in the normal PI table, in case of NOPI table no hashing, redistribution and sorting are needed . In NOPI table rows will be appended to the table’s data block, thus overhead of sorting rows based on ROWID will be avoided here. Furthermore, as the rows are assigned randomly to the AMPs, data will always be distributed evenly across all AMPs and no skewing will occur.

Changing NOPI table to PI table:-

Once NOPI table is created, we can change it to PI table in two ways-

1.   CREATE MULTISET TABLE teradatapoint_tmp AS
     (SELECT * FROM teradatapoint )
     WITH PRIMARY INDEX (COLUMN_NAME); 

Here table teradatapoint is a NOPI table.

2.  Create a new table with PI and execute insert-select from NOPI table to PI table-

CREATE MULTISET TABLE teradatapoint_tmp (
        Column1 INTEGER NOT NULL,
        Column2 DATE FORMAT ‘MM/DD/YYYY’ NOT NULL,
        Column3 INTEGER)
PRIMARY INDEX (Column1);

Insert into teradatapoint_tmp Select * from teradatapoint;

Limitation of NOPI table:-

1. Cannot create a NOPI table as a SET table.
2. MultiLoad is not supported by NOPI table.
3. No permanent journal possible.
4. Cannot define PPI, hash index.
5. Cannot use as queue and error table.
6. Update and upsert is not supported.