A PPI is a type of index that enables users to set up databases that provide performance benefits from a data locality, while retaining the benefits of scalability inherent in the hash architecture of the Teradata database. This is achieved by hashing rows to different virtual AMPs, as is done with a normal PI, but also by creating local partitions within each virtual AMP.
Normal PI access remains unchanged, but in the case of a range query for example, each virtual AMP is able to immediately focus its search on specific partitions within its workspace.
If the PPI column is specified in the join condition, the system knows the range of values in a query and it scans only the portions of the table that correspond to those dates. Table can be or have:
Non-partitioned primary index: A traditional non-partitioned PI allows the data rows of a table to be:
- Hash partitioned (that is, distributed) to the AMPs by the hash value of the primary...