Mar 21

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



Skip to comment form

  1. srikanth Kowtha

    Hi Admin,

    Can you please explain how actually the the sort works for table’s having PPI for below case .

    when there is already data in table and you do an insert to the table.

    Happy learning

  2. Ramana

    Can we create PPI even after the table is populated with records? or only at the time of table creation?

    1. anurag

      only at the time of table creation..because row are distribute base on pi and create rowid then we mention partion means again row are automatically arrenge with user define partion..hope you will understand.

  3. prasanna

    Hi Admin,

    Can u explain what are the advantages of teradata over oracle.

    for waht purpose we can use teradta instead of oracle ?

    why teradata was developed ?

    canu expalin PPi indexes with examples

  4. NR

    [ 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. ] ==> Could you please explain this by example ?

  5. Amarnath

    Hi Admin,

    Can you explain “Sliding window merger join”?

  6. Sonia

    I have one doubt PPI by range can be done on date only on what other columns PPI by range can be done?
    Can you explain PPI by case with one example?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>