In Teradata Database 13.10 is the ability to let Teradata select an appropriate index. This new feature is called No Primary Index (NOPI). When a table is created without a declared PI, then the database will default to the NOPI mechanism rather than the using the first column of the table. This functionality has been activated on all Teradata servers as of November 9th, 2012.
One requirement with the NOPI declaration is a table must be created as a MULTISET table rather than SET (uses the MULTISET keyword when creating the table). If a CREATE TABLE statement does not declare a PI and calls for a SET table type, the transaction will fail due to this requirement.
Is there any reason behind, why NOPI declaration used with MULTISET table?
This functionality has been activated on all Teradata servers as of November 9th, 2012
??? Are you talking about the TD systems in your company?
A NOPI table doesn't have a PI, that's why it's called NOPI.
SET means a logical Primary Key across all columns, a new row must be checked if it's duplicate. On a SET table this will be done for all rows with the same PI/RowHash. WIthout PI it would mean checking against every row in the table.