Row Partitioning Across AMPs for 3 Partitioning Levels
The following graphic, which is best viewed on a color monitor and best printed on a color printer, shows the partitioning of rows based on a table that has 3 partitioning levels based on the table defined in “Detailed Multilevel Partitioning Example” on page 393.
The only difference between this graphic and the data in the table for the multilevel row partitioning example is that there is only one row per combined partition number, while the graphic has anywhere between 1 and 8 rows per combined partition number.
This graphic appears again later in this chapter in support of a more detailed explanation of multilevel partitioning (see “Detailed Multilevel Partitioning Example” on page 393).
Multilevel partitioning allows each partition at a given level to be further partitioned into subpartitions. Each partition for a level is subpartitioned the same per a partitioning expression or by column partitioning defined for the next lower level. Rows are grouped by the combined partition number, and within a group are ordered first by hash value and then, if for a partitioned primary index, by uniqueness value. The combined partition numbers are mapped 1-to-1 with internal partition numbers on which the rows on an AMP are ordered. The file system orders rows by the internal partition number, rowhash value, and then uniqueness value.
For multilevel partitioning, Teradata Database hash orders the rows within the lowest partition levels. A multilevel partitioning undertakes efficient searches by using row partition elimination at the various levels or combinations of levels. See SQL Request and Transaction Processing for a description of row partition elimination and its various forms.
The following list describes the various access methods that are available when multilevel partitioning is defined for a table.
This is a single-AMP, single-hash access in a single subpartition at the lowest level of the partition hierarchy.
This is a single-AMP, single-hash access in multiple (but not all) subpartitions at the lowest level of the partition hierarchy.
This is a single-AMP, single-hash access in all subpartitions at the lowest level of the partition hierarchy.
This is an all-AMP scan of only the non-eliminated row partitions for that level. This allows multiple access paths to a subset of the data: one for each partitioning expression.
If constraints are defined on partitioning columns for more than one of the partitioning expressions in a multilevel partitioning definition, row partition elimination can lead to even less of the data needing to be scanned.