Multilevel Partitioning - Teradata Database

Multilevel Partitioning

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 341.

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 341).

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.

If there is an equality constraint on the primary index and there are constraints on the partitioning columns such that access is limited to a single partition at each level, access is as efficient as with a nonpartitioned table.

This is a single-AMP, single-hash access in a single subpartition at the lowest level of the partition hierarchy.

With constraints defined on the partitioning columns, performance of a primary index access can approach the performance of a nonpartitioned primary index depending on the extent of row partition elimination that can be achieved.

This is a single-AMP, single-hash access in multiple (but not all) subpartitions at the lowest level of the partition hierarchy.

Access by means of equality constraints on the primary index columns that does not also include all the partitioning columns, and without constraints defined on the partitioning columns, might not be as efficient as access with a nonpartitioned primary index. The efficiency of the access depends on the number of populated subpartitions at the lowest level of the row partition hierarchy.

This is a single-AMP, single-hash access in all subpartitions at the lowest level of the partition hierarchy.

With constraints on the partitioning columns of a partitioning expression such that access is limited to a subset of, say n percent, of the partitions for that level, the scan of the data is reduced to about n percent of the time required by a full-table scan.

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.