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 Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
You cannot ADD a range set to a CASE_N function in a partitioning level, but must use the MODIFY or MODIFY PRIMARY INDEX options to redefine the entire partitioning for the table.
You cannot change from 2-byte partitioning to 8-byte partitioning with ADD RANGE. You must use the MODIFY PARTITION BY or MODIFY PRIMARY INDEX options to redefine the entire partitioning for the table.
- ADD RANGE#L n
- 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 #L n in a RANGE#L n 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#L n column, where n ranges from 1 through 62, and depends on the level being modified and whether one or multiple levels are changed.