17.10 - ADD RANGE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)


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:
  • 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.