Partition Primary Index – Advantage and Disadvantage

Advantage of Partition Primary Index –

• Partitioned Primary Index is one of the unique features of Teradata, which is used for distribution of rows based on different partitions so that they can be retrieved much faster than any other conventional approach.
• Maximum partitions allowed by Teradata – 65,535

• It also reduces the overhead of scanning the complete table (or FTS) thus improving performance.
• In PPI tables row is hashed normally on the basis of its PI, but actual storage of row in AMP will take place only in its respective partition. It means rows are sorted first on the basis of there partition column and then inside that partition they are sorted by there row hash .
• Usually PPI’s are defined on a table in order to increase query efficiency by avoiding full table scans without the overhead and maintenance costs of secondary indexes.
• Deletes on the PPI table is much faster.
• For range based queries we can effectively remove SI and use PPI, thus saving overhead of SI subtable.

Disadvantage of Partition Primary Index –

• PPI rows are 2 bytes are longer so it will use more PERM space.
• In case we have defined SI on PPI table then as usual size of SI subtable will also increase by 2 bytes for each referencing rowid

• A PI access can be degraded if the partition column is not part of the PI. For e.g. if query specifying a PI value but no value for the PPI column must look in each partition for that table, hence loosing the advantage of using PI in where clause.
• When we are doing joins to non-partitioned tables with the PPI table then that join may be degraded. If one of the tables is partitioned and other one is non-partitioned then sliding window merger join will take place.
• The PI can’t be defined UNIQUE  when the portioning columns is not the part of PI.

To know more about PPI please go the link below –

Partition Primary Index – Basics