Add a set of ranges to the RANGE_N function on which a partitioning level for the table is based. You must specify the ranges to be added in ascending order. For information about the RANGE_N function, see SQL Functions, Operators, Expressions, and Predicates, B035-1145.
- ADD RANGE#Ln
- Partition level number where n
is an integer value from 1 through 15, for 2-byte partitioning, or from 1 through 62, for 8-byte partitioning. Note that there is no space between RANGE and #Ln
in a RANGE#Ln
specification. You can only add ranges if a partitioning level for the table is derived exclusively from RANGE_N functions. Use a RANGE_N function to define a mapping of ranges of the following types to INTEGER or BIGINT numbers:
- You can only add standard ranges from non-character partitioning levels. The term standard range here refers to any range other than NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN. For example, a multilevel character partitioning with one or more non-character partitioning levels.
- You can only add ranges to a character partitioning if the session collation matches the table collation and the session mode matches the session mode in effect when the table was created.
- You can only alter character partitioning levels that are defined using a RANGE_N function, and you can only alter such partitions to add or drop a NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN partition.
- The maximum number of ranges, not including the NO RANGE, NO RANGE OR UNKNOWN, and UNKNOWN partitions for a RANGE_N partitioning level, is 9,223,372,036,854,775,805.
- BETWEEN start_expression
- The start_expression is defined according to the rules for the RANGE_N function..
- AND end_expression
- Required only for the last range.
The end_expression is defined according to the rules for the RANGE_N function. .
- EACH range_size
- Equivalent to a series of start_expressions by adding multiples from 0 in increments of 1 of range_size
to the specified start_expression. A range_size
must be less than or equal to end_expression
or less than the next start_expression. The range_size
variable must be a constant expression.
You cannot specify an EACH clause if the RANGE_N function specifies a character or graphic test value.
- NO RANGE
- You can add NO RANGE or UNKNOWN to the definition for a RANGE_N function.
- WHERE conditional_expression
- You can specify a range expression or a conditional partition expression. For examples of adding ranges based on a range expression or a conditional partition expression, see Example: Dropping and Adding Partition Ranges. You must base conditional_expression on the system-derived PARTITION column or PARTITION#Ln column, where n ranges from 1 through 62, and depends on the level being modified and whether one or multiple levels are changed.