Row Partitioning - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

Primary indexes can be row partitioned or nonpartitioned. A partitioned primary index (PPI) permits rows to be assigned to user-defined data partitions on the AMPs. A PPI is defined to be either single-level or multilevel. Multilevel partitioning allows each partition to be subpartitioned.

The greatest potential gain in row partitioning a primary index is the ability to access only the rows of the qualified partitions while skipping partitions that do not contain rows meeting the search conditions of a query.

A PPI increases query efficiency by avoiding full-table scans without the overhead and maintenance costs of secondary indexes. A multilevel PPI provides multiple access paths to the rows in the base table and can improve query performance through row partition elimination at each of the various levels or combination of levels.

PPIs are designed to optimize range queries while also providing efficient PI join strategies. A range query requests data that falls within specified boundaries. A common example of this is queries that involve date ranges.

Column partitioning is not supported for tables with a primary index.

Recommendation: Consider defining a table with a PPI to support either of the following workload characteristics:

  • A majority of the queries in the workload specify range predicates on some column, particularly a date column, of the PPI table.
  • A majority of the queries in the workload specify an equality predicate on some column of the PPI table, and that column is either not the only column in the primary index column set or not a primary index column.