Teradata - Partitioned Primary Index

Partitioned Primary Index (PPI) is an indexing mechanism that is useful in improving the performance of certain queries. When rows are inserted into a table, they are stored in an AMP and arranged by their row hash order. When a table is defined with PPI, the rows are sorted by their partition number. Within each partition, they are arranged by their row hash. Rows are assigned to a partition based on the partition expression defined.

Advantages

Avoid full table scan for certain queries.

Avoid using secondary index that requires additional physical structure and additional I/O maintenance.

Access a subset of a large table quickly.

Drop the old data quickly and add new data.

Example

Consider the following Orders table with Primary Index on OrderNo.

Assume that the records are distributed between AMPs as shown in the following tables. Recorded are stored in AMPs, sorted based on their row hash.

CREATE SET TABLE Orders (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE FORMAT  YYYY-MM-DD , 
   OrderTotal INTEGER 
) 
PRIMARY INDEX(OrderNo) 
PARTITION BY RANGE_N  (
   OrderDate BETWEEN DATE  2010-01-01  AND  2016-12-31  EACH INTERVAL  1  DAY
);