No Primary Index Table in Teradata

NOPI or No Primary Index tables were introduced to make the Loading activities faster. We insert data into the table based on Random distribution. Since, no hashing is involved, data load will be significantly faster.

But, developers should take ample precaution before declaring a table as NOPI.

Example:

CREATE MULTISET TABLE PRD_DB.FACT_BSKT_SUM ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      DAY_NBR DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      LOCN_NBR INTEGER NOT NULL,
      VBS_NBR INTEGER NOT NULL,
      TRS_CNT INTEGER NOT NULL,
      TTL_BSKT_AMT DECIMAL(15,2) NOT NULL,
      TTL_BSKT_UN_QT DECIMAL(15,4) NOT NULL)
NO PRIMARY INDEX ;

Disadvatages:

Data in a NoPI table is stored by random distribution. So, when a SELECT runs on the NoPI table, we are forced to do a Full table scan.
In the absence of a Primary Index or Hash Value, a full table scan based on all-AMPs is the only way to fetch data.
Now, a full table scan uses the Master Index and Cylinder Index of the data block, instead of using any Hashing Index or USI. So, joins with NoPI tables can become significantly slow.


Let us know your topic of interest. And Please Donate if you are helped using the button on the right!!