16.10 - Partitioning Considerations - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Published
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
Last Update
2018-04-25

The decision to define a single-level or multilevel Partitioned Primary Index (PPI) for a table depends on how its rows are most frequently accessed. PPIs are designed to optimize range queries while also providing efficient primary index join strategies and may be appropriate for other classes of queries. Performance of such queries is improved by accessing only the rows of the qualified partitions.

A PPI increases query efficiency by avoiding full table scans without the overhead and maintenance costs of secondary indexes.

The most important factors for PPIs are accessibility and maximization of partition elimination. In all cases, it is critical for parallel efficiency to define a primary index that distributes the rows of the table fairly evenly across the AMPs.