ADD RANGE - Advanced SQL Engine - Teradata Database


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.

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:

• BIGINT
• BYTEINT
• CHARACTER
• DATE
• GRAPHIC
• INTEGER
• SMALLINT
• VARCHAR
• VARGRAPHIC

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

UNKNOWN

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.