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.
- 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.
- 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, the system returns an error to the requestor.
- If you do not specify an ADD clause for a partitioning level and that level 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 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 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, the system 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.
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.
- For single-level partitioning, Vantage adds the maximum excess combined partitions to the partitioning level, overriding any specified ADD clause.
- For multilevel partitioning, Vantage adds excess combined partitions to partitioning levels as follows:
- If all row partitioning levels have an ADD clause, but a column partitioning level has no ADD clause, Vantage adds the maximum excess combined partitions to the column partitioning level (which need not be the first partitioning level).
- If a column partitioning level and at least one row partitioning level has no ADD clause, Vantage adds the maximum excess combined partitions to the first row partitioning level without an ADD clause after using a default of ADD 10 for the column partitioning level.
Vantage repeats this action for each row partitioning level without an ADD clause, in level order.
- If there is no column partitioning or a column partitioning level has an ADD clause, and at least one row partitioning level has no ADD clause, Vantage adds the maximum excess combined partitions to the first row partitioning level without an ADD clause.
Vantage repeats this action for each row partitioning level without an ADD clause, in level order.
- If all partitioning levels have an ADD clause, Vantage adds the maximum excess combined partitions to the first row or column partitioning level,overriding any specified ADD clause.Vantage repeats this action for each remaining level if any of the following conditions are true:
- At least one level has an ADD clause.
- At least one level consists solely of a RANGE_N function with BIGINT data type.
- There is column partitioning.
- The partitioning is 8-byte.
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 for an explanation of the function of the NO RANGE partition).
Therefore, 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 Vantage 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 when the ALTER TABLE statement succeeds.
- 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 a value between 1 and 65,535 (after casting to INTEGER if not already typed as INTEGER or CHARACTER) is an error.
- Increasing the number of active row partitions for a partitioned table can degrade the performance of primary index accesses and joins, but allows for finer row partition elimination (see Row Partition Elimination 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, the system 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, the system 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.
| Session Mode | Default |
|---|---|
| ANSI | CASESPECIFIC |
| Teradata | NOT CASESPECIFIC |
- CHAR2HEXINT
- INDEX
- LOWER
- MINDEX
- POSITION
- TRANSLATE
- TRANSLATE_CHK
- TRIM
- UPPER
- VARGRAPHIC