Drop a set of ranges from the RANGE_N function on which a partitioning level for the table is based. You must specify the ranges to be dropped in ascending order. Use a conditional expression to drop a range set from the RANGE_N function on which a partitioning level for the table is based. You cannot DROP a condition from a CASE_N function in a partitioning. You must use PARTITION BY to redefine the entire partitioning for the table.
For information about the RANGE_N function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
- DROP RANGE#L n
- Represents a partition level number where n is an integer between 1 and 15, inclusive, for 2-byte partitioning and between 1 and 62, inclusive for 8-byte partitioning. You can only drop ranges from a character partition if the partitioning level for the table is derived exclusively from a RANGE_N function. You can only drop standard ranges from non-character partitioning levels. The term standard range refers to any range other than NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN. For example, a standard range could be a multilevel character partitioning with one or more non-character partitioning levels. You can only drop ranges in a character partitioning if the session collation matches the table collation and the session mode is the same as 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. You can only alter such character partitioning levels to add or drop a NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN partition. You can only drop standard ranges from non-character partitioning levels. For example, a multilevel character partitioning with one or more non-character partitioning levels. You can only drop ranges from a character partitioning if the session collation matches the table collation and the session mode is the same as the session mode in effect when the table was created. You can only alter character partitioning levels that are defined using RANGE_N functions, and you can only alter such partitions to add or drop a NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN partition.
- BETWEEN start_expression
- The start_expression must be defined according to the rules for the RANGE_N function. #L n represents a partition level number where n is an integer between 1 and 15, inclusive, for 2-byte partitioning and between 1 and 62, inclusive for 8-byte partitioning.
- AND end_expression
- Required only for the last range. The end_expression must be defined according to the rules for the RANGE_N function.
- EACH range_size
- A range with an EACH clause is 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. Each range_size must be a constant expression.
- NO RANGE
- UNKNOWN
- You can drop NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN specifications from the definition for a RANGE_N function.
- WHERE conditional_expression
- You can specify a range expression or a conditional partition expression. For examples of dropping 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.