16.10 - Selecting the Partitioning Granularity - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

The key guideline for determining the optimum granularity for the partitions of a partitioned table or join index is the nature of the workloads that most commonly access the object. The higher the number of partitions you define for an object, the more likely an appropriate range query against the table will perform more quickly, given that the partition granularity is such that the Optimizer can eliminate all but one partition or a few partitions.

On the other hand, it is generally best to avoid specifying too fine a partition granularity. For example if query workloads never access data at a granularity of less than one month, there is no benefit to be gained by defining partitions with a granularity of less than one month. Furthermore, unnecessarily fine partition granularity is likely to increase the maintenance load for a partitioned table, which can lead to degradation of overall system performance. In the end, even though too fine a partition granularity itself does not itself introduce performance degradations, the underlying maintenance on such a table can indirectly degrade performance.

Determining the Maximum Number of Partitions for a Partitioned Database Object

When you define a partitioned database object, the question of how many partitions to define per partitioning level must be addressed.

If the table or join index is defined with multiple partitioning levels, you should explicitly specify the number of partitions per partitioning level using the ADD option (see “CREATE TABLE” in SQL Data Definition Language). If you do not, Teradata Database reserves any excess partitions for partitioning level 1, and these partitions cannot be added to any other partitioning level once the table or join index is created.

The maximum number of partitions that are available for the level 1 partition from excess partitions depends on whether you have defined the object with 2-byte partitioning or with 8-byte partitioning.

  • For a column-partitioning level, if
    • You do not specify an ADD clause for the column-partitioning level
    • There is also row partitioning

      AND

    • At least one of the row-partitioning levels does not specify an ADD clause

      Then the maximum number of partitions for the column-partitioning level is the number of column partitions defined plus 10.

  • For a column-partitioning level, if
    • You do not specify an ADD clause for the column-partitioning level
    • There is also row partitioning
    • All of the row-partitioning levels specify an ADD clause

      AND

    • Using the number of column partitions defined plus 10 as the maximum number of column partitions, the partitioning would be 2-byte partitioning

    Then the maximum number of partitions for the column-partitioning level is the largest value that does not cause the partitioning to be 8-byte partitioning.

    Otherwise, the maximum number of partitions for the column-partitioning level is the largest value that does not cause the maximum combined partition number to exceed 9,223,372,035,854,775,807.

    For single-level partitioning, the maximum combined partition number is the same as the maximum partition number for the partitioning level.

  • For a column-partitioning level, the maximum partition number is 1 more than the maximum number of partitions for that level. This is so that at least 1 unused column partition number is always available for altering a column partition.
  • For each row-partitioning level without an ADD clause in level order, the maximum number of partitions for the row-partitioning level is the largest value that does not cause the partitioning to be 8-byte partitioning if using the number of row partitions defined as the maximum for this and any lower row-partitioning level without an ADD clause the partitioning would be 2-byte partitioning, is the largest value that does not cause the partitioning to be 8-byte partitioning.

    Otherwise, the maximum number of partitions for this level is the largest value that does not cause the maximum combined partition number to exceed 9,223,372,035,854,775,807.

  • The maximum number of partitions for a row-partitioning level is at least 2.

    Otherwise, Teradata Database aborts the request and returns an error to the requestor. This error occurs when only one partition is defined for a row-partitioning level with an ADD 0 or with no ADD option and the maximum is not increased to at least 2.

  • Having a maximum number of partitions for a partitioning level that is larger than the number of defined partitions for that level might enable the number of defined partitions for that level to be increased using an ALTER TABLE request.

    The maximum number of partitions for a level cannot be increased.

  • For a row-partitioning level, the maximum partition number is the same as the maximum number of partitions for that level.
  • The maximum combined partition number is the product of the maximum partition number determined for each partitioning level. The value cannot exceed 9,223,372,036,854, 775,807; otherwise, Teradata Database aborts the request and returns an error to the requestor.

    For single-level partitioning, the maximum combined partition number is the same as the maximum partition number for the partitioning level.

  • The number of combined defined partitions is the product of the number of defined partitions for each partitioning level.
  • The maximum number of combined partitions is the product of the maximum number of partitions determined for each partitioning level.

    For single-level partitioning, the maximum number of combined partitions is the same as the maximum number of partitions for the partitioning level.

    If there is column partitioning, the maximum number of combined partitions is smaller than the maximum combined partition number or it is the same.

  • If the maximum combined partition number is greater than 65,535, the partitioning is 8-byte partitioning; otherwise, it is 2-byte partitioning.

    Single-level column partitioning with no ADD option is 2-byte partitioning.

  • The rules listed in the following table apply to 2-byte partitioning and to 8-byte partitioning for both row-partitioned and column-partitioned tables and join indexes.
For this partitioning … The maximum number of partitions for the first level is increased to … And the maximum number of partitioning levels is …
2-byte the largest value that does not cause the maximum combined partition number to exceed 65,535 if it does not already do so.

If there is at least one level with an explicit ADD clause, there is at least one level that consists solely of a RANGE_N function with BIGINT data type, or there is column-partitioning, this is repeated for each of the other levels, if any, from the second level to the last level.

15
8-byte the largest value that does not cause the maximum combined partition number to exceed 9,223,372,036,854,775, 807 if it does not already do so.

This is repeated for each of the other levels from the second level to the last level.

62

You can alter any level to have between 1 and the maximum number of partitions allowed for that partitioning level.

Assigning Excess Combined Partitions to Partitioning Levels

Teradata Database assigns leftover partitions to different partitioning levels depending the following set of rules.

IF the partitioning is … THEN Teradata Database adds as many leftover combined partitions as possible to the …
single-level first and only row or column partitioning level and the ADD value is overridden if one is specified.
multilevel and …
  all the row partitioning levels have an ADD clause, but there is a column partitioning level without an ADD clause column-partitioning level, which does not need to be at the first partitioning level.
  a column partitioning level and at least one of the row partitioning levels does not specify an ADD clause, including the case where none of the row partitioning levels specify an ADD clause first row-partitioning level without an ADD clause after using a default of ADD 10 for the column-partitioning level.

This is repeated for each of the other row partitioning levels without an ADD clause in partitioning level order.

  a column partitioning level has an ADD clause and at least 1 of the row partitioning levels does not specify an ADD clause first row partitioning level without an ADD clause.

This is repeated for each of the other row partitioning levels without an ADD clause in partitioning level order

  there is no column partitioning level and at least 1 of the row partitioning levels does not have an ADD clause, including the case where none of the row partitioning levels specify an ADD clause
  all the partitioning levels specify an ADD clause or after applying leftover combined partitions first row or column partitioning level and the ADD clause for the first partitioning level, if specified, is overridden.

If there at least 1 level that specifies an explicit ADD clause, there is at least 1 level that consists only of a RANGE_N function with BIGINT data type, there is column partitioning, or the partitioning is 8-byte, this is repeated for each of the other levels from the second level to the last level.