15.00 - Rules For Altering the Partitioning Expression For a Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules For Altering the Partitioning Expression For a Table

The following general rules and observations apply to the MODIFY [PRIMARY INDEX] option when you alter partitioning expressions. The rules apply to row‑partitioned tables and join indexes, and row partitioning levels of a multilevel column‑partitioned table or join index.

See “Modifying the Partitioning of a Table Using the ADD RANGE and DROP RANGE Options” on page 96 and “Rules for Altering the Partitioning Expression for Multilevel Partitioning” on page 122, “Modifying the Partitioning of a Table Using the ADD RANGE and DROP RANGE Options” on page 96, and “Rules for Modifying a Character Partitioning Expression” on page 121 for more information.

  • If statistics, whether single‑column or multicolumn, have been collected on the system‑derived PARTITION column of a table, you must drop those statistics before you can alter the partitioning expression for the table.
  • The high‑level procedure for altering the partitioning expression of a table is as follows.

    a Drop the statistics on the system‑derived PARTITION column.

    b Alter the partitioning expression.

    c Recollect PARTITION statistics.

  • You can write any valid SQL expression as a partitioning expression with the following exclusions.
  • User‑defined functions
  • Aggregate functions
  • Grouped row ordered analytic functions
  • Built-in functions
  • The RANDOM function
  • Note: This restriction does not apply to the HASH BY clause in an INSERT or INSERT … SELECT request. See SQL Data Manipulation Language for more information about the HASH BY clause.

  • The HASHAMP and HASHBAKAMP functions
  • Note: The HASHROW and HASHBUCKET functions are permitted in a row partitioning expression for both row‑partitioned and column‑partitioned tables and join indexes.

  • The system-derived PARTITION and PARTITION[#Ln] columns
  • The UPPERCASE attribute
  • Set operators
  • Subqueries
  • If the data type for the result of partitioning_expression is not INTEGER, BIGINT, or CHARACTER, then the value is implicitly cast to the INTEGER type.
  • If the result type cannot be cast to INTEGER, Teradata Database aborts the request and returns an error to the requestor.

  • If partitioning_expression for a partitioning level specifies only a CASE_N function, then the number of conditions defined must be less than or equal to 2,147,483,647 (see the documentation for the CASE_N function in SQL Functions, Operators, Expressions, and Predicates).
  • When you define ranges explicitly (not using an EACH clause), other size limits such as those for table headers, request text, or implied partitioning CHECK constraints are likely to be reached before you exceed the limit of 2,147,483,647 CASE_N conditions.

  • If partitioning_expression for a partitioning level specifies only a RANGE_N function with INTEGER data type, the number of ranges defined for the function must be less than or equal to 2,147,483,647 (see the documentation for the RANGE_N function in SQL Functions, Operators, Expressions, and Predicates).
  • If partitioning_expression for a partitioning level specifies only a RANGE_N function with BIGINT data type, the number of ranges defined for the function must be less than or equal to 9,223,372,036,854,775,805 for 8‑byte partitioning or 65,533 for 2‑byte partitioning (see the documentation for the RANGE_N function in SQL Functions, Operators, Expressions, and Predicates).
  • The number of defined partitions for a row partitioning level is the number of row partitions specified by the RANGE_N or CASE_N function, or 65,535 if you do not specify the RANGE_N or CASE_N functions to define a partitioning level.
  • If you specify an ADD clause, the maximum number of partitions for a partitioning level is the number of defined partitions for that level plus the value of the INTEGER constant specified by the ADD clause.
  • If this maximum exceeds 9,223,372,036,854,775,807 for 8‑byte partitioning or 65,535 for 2‑byte partitioning, Teradata Database aborts the request and returns an error to the requestor.

  • If you do not specify an ADD clause for a partitioning level and it is the only level of partitioning for the table, the maximum number of partitions for that level, including the two partitions reserved for internal use, is 65,534.
  • If the following things are true for a column‑partitioned table or join index, the maximum number of partitions for the column partitioning level is the number of column partitions you define plus 10. The default in this case is ADD 10.
  • You do not specify an ADD clause for the column partitioning level
  • the table is also row‑partitioned
  • at least one of the row partitioning levels does not specify an ADD clause
  • If the following things are true for a column partitioning level, 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.
  • You do not specify an ADD clause for the column partitioning level and the table is also row‑partitioned.
  • All of the row partitioning levels specify an ADD clause.
  • Using the number of column partitions defined plus 10 as the maximum number of column partitions, the table or join index has 2‑byte partitioning.
  • Otherwise, the maximum number of partitions for the column partitioning level is the largest value that does not cause the limit to exceed 9,223,372,036,854,775,807.

    If there is no such largest value, Teradata Database aborts the request and returns an error to the requestor.

  • If the following things are true for a partitioned table, the maximum number of partitions for each row partitioning level is the largest value that does not cause the partitioning to become 8‑byte partitioning.
  • The row partitioning level does not specify an ADD clause.
  • The number of defined row partitions is the current maximum for this and any lower row partitioning level without an ADD clause, a table has 2‑byte partitioning
  • Otherwise, the maximum number of partitions for the level is the largest value that does not cause the combined partition number to exceed 9,223,372,036,854,775,807 for 8‑byte partitioning or 65,535 for 2‑byte partitioning.

  • You can specify ADD 0 for a partitioning level to specify that the maximum number of partitions for this level is the same as the number of defined partitions.
  • For a column partitioning level, this is useful if you want to override the default of ADD 10 so that other levels can have more partitions.
  • For a row partitioning level, this is useful if you want a lower level that does not specify the ADD clause to have any excess partitions.
  • The maximum number of partitions for a row partitioning level must be at least 2.
  • If it is not, Teradata Database aborts the request and returns an error to the requestor.

    This error occurs when only 1 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.

  • The following table summarizes to which partitioning levels Teradata Database adds any excess combined partitions.
  •  

    IF the partitioning is …

    AND …

    THEN as many leftover combined partitions as possible are added to …

    single‑level

     

    the first and only row or column partitioning level.

    If an ADD value is specified, Teradata Database overrides it.

    multilevel

     

     

     

     

    all the row partitioning levels have an ADD clause, but there is a column partitioning level without an ADD clause

    the column partitioning level, which does not need to be the first partitioning level.

    a column partitioning level and at least one of the row partitioning levels does not have an ADD clause, including the case where none of the row partitioning levels have an ADD clause specified

    the first row partitioning level without an ADD clause after using a default of ADD 10 for the column partitioning level.

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

    a column partitioning level has an ADD clause and at least one of the row partitioning levels does not have an ADD clause

    the first row partitioning level without an ADD clause.

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

     

    there is no column partitioning level and at least one of the row partitioning levels does not have an ADD clause, including the case where none of the row partitioning levels has an ADD clause specified

    all the partitioning levels have an ADD clause or after applying leftover combined partitions as defined in the next column of this table

    the first row or column partitioning level and Teradata Database overrides the ADD clause for the first partitioning level if one is specified.

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

  • If a new partitioning expression is defined with a NO RANGE partition, by definition this partition contains any row that does not fall into an explicitly defined partitioning expression value range (see the description of the RANGE_N function in SQL Functions, Operators, Expressions, and Predicates for an explanation of the function of the NO RANGE partition).
  • As a result, once a range partition is dropped, any row that had been assigned to that partition must then be assigned to the NO RANGE partition.

  • If evaluation of a new partitioning expression for a row causes evaluation errors (such as divide by zero), then Teradata Database rolls back any changes made to the table or to save_table and does not modify the partitioning.
  • The new partitioning expressions become the effective partitioning expressions for the table as soon as it is altered successfully.
  • If there is a subsequent attempt to insert or update a row of a row‑partitioned table or a column‑partitioned table that also has row partitioning such that the partitioning expression for that row does not generate, after casting to INTEGER if it is not already typed as INTEGER or CHARACTER, a value between 1 and 65,535, then the system returns an error for the insert or update operation.
  • Increasing the number of active row partitions for a partitioned table might degrade the performance of primary index accesses and joins, but allows for finer row partition elimination (see Database Design for details).
  • The general usage rules for altering a character‑row‑partitioned table or join index are the same as the rules for non‑character row‑partitioned tables or join indexes only for the following cases.

  • The table or join index has no rows and the partitioning is changed by specifying a new partitioning expression.
  • If the table or join index is populated with rows, Teradata Database aborts the request and returns an error to the requestor.

  • The table or join index is character‑row-partitioned with one or more row partitioning levels that do not specify character data comparisons.
  • You can only add or drop non‑character row partitioning levels for the following cases.

  • The partitioning levels are defined with a RANGE_N function
  • The session collation must be identical to the table or join index collation
  • The session transaction semantics are the same as those that were in effect when the table or join index was created
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • You can only alter character row partitioning levels that are defined using a RANGE_N function.
  • You can only alter character row partitioning levels to add or drop a NO RANGE partition, an UNKNOWN partition, or both.
  • Also see “Rules for Altering the Row Partitioning for Character Partitioning” on page 116 and “Rules for Modifying a Character Partitioning Expression” on page 121.

    The usage rules for using CASE_N partitioning expressions in a character‑row‑partitioned table or join index or a column‑partitioned table or join index that also defines at least one character‑row‑partitioning level created with an ALTER TABLE request are the same as those for a non‑character‑row‑partitioned table or join index with the following exceptions.

  • If you attempt to change the character‑based row partitioning of a table or join index using an ALTER TABLE … MODIFY | MODIFY PRIMARY INDEX … PARTITION BY request, then the table or join index must be empty. If it is not, the request aborts and Teradata Database returns an error message to the requestor.
  • This is true for both row‑partitioned tables or join indexes and for column‑partitioned tables or join indexes that also specify one or more character row partitioning levels in their partitioning expression.

  • If you attempt to change a character partitioning level of a column‑partitioned table or join index, you must specify an ALTER TABLE … MODIFY … NO PRIMARY INDEX … PARTITITION BY request.
  • If you specify ALTER TABLE … MODIFY … PRIMARY INDEX … PARTITITION BY, Teradata Database aborts the request and returns an error to the requestor.

  • If the new partitioning expression is for a character‑row‑partitioned table or join index, the session collation in effect during the ALTER TABLE request becomes the collation of the modified table.
  • This is true for both character‑row‑partitioned tables or join indexes and for character row partitioning levels of a column‑partitioned table or join index.

  • You cannot alter the partitioning of a character‑row‑partitioned table or join index using ADD RANGE or DROP RANGE clauses for CASE_N expressions.
  • Teradata Database determines the case sensitivity of character column references and literals, which also affects comparison evaluation, based on the session default or any explicit CAST expression in the CREATE TABLE request when the table is created or in the CREATE JOIN INDEX request when the join index is created.

    You can also explicitly assign columns to be CASESPECIFIC or NOT CASESPECIFIC, and you can also CAST constant expressions with those qualifiers.

     

    FOR this session mode …

    Teradata Database uses the following default case specificity …

    ANSI

    CASESPECIFIC

    Teradata

    NOT CASESPECIFIC

    A CASE_N partitioning expression can specify the UPPERCASE column attribute and the following functions.

  • CHAR2HEXINT
  • INDEX
  • LOWER
  • MINDEX
  • POSITION
  • TRANSLATE
  • TRANSLATE_CHK
  • TRIM
  • UPPER
  • VARGRAPHIC