General Guidelines
The following general rules and observations apply to the MODIFY option when you alter the partitioning of row-partitioned tables.
For more information, see Modifying Partitioning Using the ADD RANGE and DROP RANGE Options, Rules for Altering the Partitioning Expression for Multilevel Partitioning, and Modifying Partitioning Using the ADD RANGE and DROP RANGE Options.
- 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, the system returns an error to the requestor.
- 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 description of the RANGE_N function in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
- 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 description of the RANGE_N function in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
- If you do not specify the RANGE_N or CASE_N functions to define a partitioning level, 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,535l.
Using the ADD Clause
- 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 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:
- 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,
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.
- If the following things are true for a column partitioning level:
- 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,
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 limit to exceed 9,223,372,036,854,775,807.
If there is no such largest value, Teradata Database returns an error to the requestor.
- If the following things are true for a partitioned table:
- 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,
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.
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 in the following cases:
- 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 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. |
Row Partitioning
- 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
Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 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 Teradata Vantage™ - Database Design, B035-1094 for details).
- The table has no rows and the partitioning is changed by specifying a new partitioning expression.
If the table is populated with rows, Teradata Database returns an error to the requestor.
- The table 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 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.
Using CASE_N
- To change the character-based row partitioning of a table using an ALTER TABLE … MODIFY … PARTITION BY statement, the table must be empty.
This applies to row-partitioned tables and column-partitioned tables that also specify one or more character row partitioning levels in their partitioning expression.
- If the new partitioning expression is for a character-row-partitioned table, the session collation in effect during the ALTER TABLE statement becomes the collation of the modified table.
- You cannot alter the partitioning of a character-row-partitioned table using ADD RANGE or DROP RANGE clauses for CASE_N expressions.
The system 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 statement when the table 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 |
- CHAR2HEXINT
- INDEX
- LOWER
- MINDEX
- POSITION
- TRANSLATE
- TRANSLATE_CHK
- TRIM
- UPPER
- VARGRAPHIC