What is Partitioned Primary Index (PPI) and what are the advantages of using it in a query?
Partitioned Primary Index (PPI) is an indexing mechanism that improves the performance of certain queries. Within a table, with Partitioned Primary Index (PPI) defined, rows are sorted according to their partition number.
Within each partition, records are arranged by their row hash. Also, rows are assigned to a partition based on the definition of a partition.
Advantages of Partitioned Primary Index (PPI):
- For several queries, PPI helps to avoid a full table scan and only required partitions are accessed.
- Prevents using the secondary index and it helps to avoid additional input/output maintenance.
- Quick access to the subset of a large table.
- Easy to drop old data and add new data.