15.10 - Row Partitioning - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

A table or join index uses row partitioning to assign rows to row partitions based on the row partitioning expressions used to define the object. A row partition consists of 0 or more rows of a table or join index that have the same value for a partitioning expression defined for the database object and that are stored separately from other row partitions of the object.

The primary advantage presented by row partitioning is using row partition elimination as an aid to optimizing queries made on row‑partitioned database objects.

See the following topics for more information about row partitioning.

  • “Row Partition Elimination” on page 317
  • “Static Row Partition Elimination” on page 325
  • “Delayed Row Partition Elimination” on page 338
  • “Product Joins With Dynamic Row Partition Elimination” on page 400
  • “Product Join With Dynamic Row Partition Elimination for Character Partitioning” on page 403
  • With the large number of partitions that can be defined for a table or join index, it is very likely that a high percentage of those partitions are empty at any given time. For example, a table on a 200 AMP system that defines 100,000 combined partitions with 100 rows per data block and 100 data blocks per each combined partition per AMP has 200 billion rows. This is a relatively small number of combined partitions when you consider that the maximum for a table or join index is 9,223,372,036,854,775,807 combined partitions.

    If each row were 100 bytes in length, the primary data alone consumes 20 petabytes of disk. That is 20 x 1015 bytes. It is highly unlikely that every combined partition would be populated. When you consider a multidimensional use of multilevel partitioning, you can easily deduce that not all combinations of dimension values actually occur.

    In general, a populated combined partition should have either many data blocks per AMP or no data blocks. For the example proposed in the first paragraph, if there is actually only 200 gigabytes of data and each populated combined partition had 100 data blocks per AMP, about 99% of the combined partitions are empty.