General Rules and Guidelines
The following rules and guidelines apply to modifying table partitioning by adding ranges to and dropping ranges from partitioning expressions using the ADD RANGE and DROP RANGE options. For additional rules that apply to character partitioning expressions, see Rules for Modifying Populated and Unpopulated Tables and Join Indexes.
Unless otherwise indicated, these rules apply to both row-partitioned tables and join indexes and to the row partitioning levels of column-partitioned tables and join indexes.
These rules only allow modification of RANGE_N partitioning expressions that are already defined for the table. You cannot use these options to change the number of partitioning levels for a table, nor can you use them to drop or add partitioning levels.
You cannot use an ALTER TABLE ... ADD RANGE[#L n] or an ALTER TABLE ... DROP RANGE[#L n] request to alter the partitioning of a table from partitioned to nonpartitioned.
- Newly added range partitions.
- The NO RANGE partition.
- The NO RANGE OR UNKNOWN partition.
Similarly, rows can be moved from the NO RANGE partition to newly added range partitions.
To delete the rows of a partition for the situation where they have other partitions they could be moved into (such as NO RANGE, NO RANGE OR UNKNOWN, or a newly added partition), use a DELETE request to remove them from the table before you alter its partitioning expressions.
- Do not overlap new ranges with dropped ranges. For example, do not DROP 2 ranges and ADD one range that covers both of the original 2 ranges, or DROP up to all but 1 range and ADD back new ranges.
- DROP RANGE ... WITH DELETE and DROP RANGE ... WITH INSERT INTO save_table do not necessarily delete rows in the dropped ranges.
Changing Partitions Using the PARTITION BY Clause
To change the number of partitioning levels or to modify a partitioning level that is not based only on a RANGE_N function, the table cannot be populated with rows and you must use the PARTITION BY clause to specify the desired partitioning levels and expressions. See Rules for Modifying Populated and Unpopulated Tables and Join Indexes.
To change the number of partitioning levels or to modify a partitioning expression that is neither based only on a RANGE_N function nor based only on a CASE_N function, you must use the PARTITION BY clause in an ALTER TABLE request to specify all the desired partitioning levels and expressions and the table must be empty. See Rules for Modifying Populated and Unpopulated Tables and Join Indexes.
This type of partitioning expression is only valid for single-level partitioning. All multilevel partitioning expressions must be based only on a RANGE_N function or only on a CASE_N function. The two functions can be mixed within a partitioning expression, but you can only specify each of them once per partitioning level of the partitioning expression.
If that is not possible, or if the reorganization of partitions would cause too many rows to be moved from their current partition to a different partition, you can use a CREATE TABLE request to create a new table with the partitioning you want, then use a MERGE or INSERT ... SELECT request with error logging to move the rows from the source table into the newly created target table with the desired partitioning.
See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for information about the INSERT ... SELECT and MERGE statements and their LOGGING ERRORS option, and CREATE ERROR TABLE for information about error tables.
Note the following rules exist for modifying partitioning:- You cannot specify NO PRIMARY INDEX without altering partitioning for a table without a primary index.
- You cannot specify NO PRIMARY INDEX with NOT PARTITIONED for a table without a primary index, primary AMP index, or partitioning.
- You cannot specify NO PRIMARY INDEX without altering partitioning for a table that has a partitioned primary index without column partitioning.
- You cannot specify NO PRIMARY INDEX with a DROP RANGE#n clause, an ADD RANGE#n clause, or both, for a table that has a partitioned primary index without column partitioning.
- You cannot alter the partitioning of a populated partitioned table to have a new partitioning with DROP clauses, ADD clauses, or both unless the new partitioning expressions are defined using DROP RANGE[#L n] and ADD RANGE[#L n] clauses that do not drop ranges other than from the beginning or end of the range sequence with at least one remaining range, and then does not add ranges between the resulting beginning and end of the ranges.
If the table has multilevel partitioning and the modification of a partitioning expression includes dropping the NO RANGE [OR UNKNOWN} or UNKNOWN partitions, the partitioning expression must not have been modified previously.
The resulting number of partitions for a level must be between 1 and the maximum defined for that level.
- You cannot ADD or DROP partitioning expressions that are based on the CASE_N function.
To modify a partitioning expression that is based on the CASE_N function, you must use the MODIFY [PRIMARY INDEX] option to redefine the entire PARTITION BY clause (see Redefining the Primary, Primary AMP, or Partitioning for a Table, General Rules for the MODIFY PRIMARY Clause), Modifying the Partitioning of a Table or Join Index, and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144).
- You can alter the row partitioning of a table or join index by explicitly specifying new partitioning expressions only if the table is empty (see Rules for Modifying Populated and Unpopulated Tables and Join Indexes).
Using DROP RANGE
The following table lists conditions that are true for partitioning when you specify DROP RANGE:
If... | Then... | Otherwise, |
---|---|---|
you specify DROP RANGE without a partitioning level number in the first alter partitioning expression, | it is equivalent to DROP RANGE#L n where n is equal to the number of the first partitioning level that is defined using only a RANGE_N partitioning expression. | if there is no such level, the system returns an error to the requestor. |
you specify DROP RANGE without specifying a level number in other than the first alter partitioning expression, | DROP RANGE is equivalent to DROP RANGE#L n, where n is equal to the number of the next partitioning level that is specified using only a RANGE_N partitioning expression after the partitioning level for the preceding alter partitioning expression. | if there is no such level, the system returns an error to the requestor. |
you specify DROP RANGE[#L n] in other than the first alter partitioning expression, | the value for n must be at least 1 greater than the default or specified level associated with the preceding alter partitioning expression. | the system returns an error to the requestor. |
you specify the default or specified level for is DROP RANGE#L n, | the value for n must not exceed the number of partitioning levels currently defined for the table. | the system returns an error to the requestor. |
you drop a range or partition, | it does not necessarily cause rows to be deleted. | affected rows could be moved to an added range or to the optional NO RANGE [OR UNKNOWN] or UNKNOWN partitions. |
you specify a DROP RANGE WHERE partition_conditional_expression for both multilevel row-partitioned tables and multilevel column-partitioned tables, | the specification must reference the system-derived column PARTITION#L n, where the value for n ranges from 1 - 62, inclusive, and is the same as the default or specified level of the corresponding DROP RANGE[#L n] and no other columns. |
the system returns an error to the requestor. |
the table definition specifies an explicit column named PARTITION#L1 for both multilevel row-partitioned tables and multilevel column-partitioned tables, | you cannot specify PARTITION#L1 in the DROP RANGE WHERE partition_conditional_expression specification of a DROP RANGE[#L n] clause. |
the system returns an error to the requestor. |
you specify a WITH DELETE clause for a multilevel partitioning expression, | there must be a DROP RANGE[#L n] in at least one of the alter partitioning expressions. |
the system returns an error to the requestor. |
a single-level row-partitioned table definition specifies an explicit column named PARTITION, | you cannot specify PARTITION in the WHERE partition_conditional_expression specification of a DROP RANGE WHERE clause. |
the system returns an error to the requestor. |
you specify a WITH INSERT clause for a multilevel partitioning expression, | there must be a DROP RANGE[#L n] in at least one of the alter partitioning expressions. |
the system returns an error to the requestor. |
working in a single-level row-partitioned table, | a DROP RANGE WHERE clause conditional expression must reference either the system-derived column PARTITION or the system-derived column PARTITION#L1, but not both, and no other columns. |
the system returns an error to the requestor. |
Using ADD RANGE
The following table lists conditions that are true for partitioning when you specify ADD RANGE:
If... | Then... | Otherwise, |
---|---|---|
you specify an ADD RANGE expression without also specifying a level in the same ALTER TABLE request as a DROP RANGE[#L n] expression in an alter partitioning expression, | the operation is equivalent to an ADD RANGE#L n expression, where the value for n is the same as the default or specified level number of that DROP RANGE[#L n]. | |
you specify an ADD RANGE expression without a level and without a DROP RANGE[#L n] expression in the first alter partitioning expression for a multilevel partitioned table, | that ADD RANGE expression is equivalent to ADD RANGE#L n, where the value for n is equal to the number of the first partitioning level that is defined only with a RANGE_N partitioning expression. | if there is no such level, the system returns an error to the requestor. |
you specify an ADD RANGE expression without specifying a level and without also specifying a DROP RANGE[#L n] expression in other than the first alter partitioning expression for a multilevel partitioned table, | that ADD RANGE expression is equivalent to ADD RANGE#L n, where the value of n is equal to the level number of the next partitioning level that is defined using only a RANGE_N partitioning expression after the partitioning level for the preceding alter partitioning expression. | if there is no such level, the system returns an error to the requestor. |
you specify an ADD RANGE expression with a DROP RANGE[#L n] in an alter partitioning expression, | it is equivalent to ADD RANGE[#L n], where n is the same as the default or specified level of that DROP RANGE[#L n]. | |
you specify an ADD RANGE[#L n] clause with a DROP RANGE[#L n] clause in an alter partitioning expression, | the value for n must specify the same level as the default or specified level of that DROP RANGE[#L n] expression. | the system returns an error to the requestor. |
you specify an ADD RANGE clause without a DROP RANGE[#L n] in the first alter partitioning expression of a multilevel partitioning expression, | it is equivalent to ADD RANGE[#L n], where n is equal to the number of the first partitioning level that is defined solely with a RANGE_N partitioning expression. | the expression is equivalent to ADD RANGE[#L n], where the value of n is one greater than the level associated with the preceding alter partitioning expression. If there is no such level, the system returns an error to the requestor. |
you specify an ADD RANGE[#L n] expression without also specifying a DROP RANGE[#L n] expression in other than the first alter partitioning expression of a multilevel partitioning expression, | the value for n must be at least one greater than the default or specified level associated with the preceding alter partitioning expression. | the system returns an error to the requestor. |
you specify an ADD RANGE[#L n] expression for a multilevel partitioning expression, | the value for n must not exceed the number of partitioning levels currently defined for the table. | the system returns an error to the requestor. |
Using DROP RANGE or ADD RANGE
- You cannot use an ALTER TABLE ... ADD RANGE[#L n] or an ALTER TABLE ... DROP RANGE[#L n] request to alter the partitioning of a table from partitioned to nonpartitioned.
- DROP RANGE and ADD RANGE modify an existing partitioning
expression to create a new partitioning expression for the table.
Vantage deletes rows only if they do not satisfy this new partitioning expression.
If the new partitioning expression specifies the NO RANGE option, then no rows are deleted.
- If you specify DROP RANGE#L n or ADD RANGE#L n, the partitioning at level n must be a partitioning expression that consists only of a RANGE_N function that does not compare character or graphic data. Otherwise, the system returns an error to the requestor.
- If you specify a DROP RANGE#L n or ADD RANGE#L n expression for a multilevel partitioning expression, the partitioning at level n must be a partitioning expression that is specified using only a RANGE_N function that does not compare character or graphic data. Otherwise, the system returns an error to the requestor.
- If you specify a DROP RANGE or ADD RANGE expression for a partitioned table without also specifying a level number, the partitioning at the default level for the options must not compare character or graphic data. Otherwise, the system returns an error to the requestor.
- If you specify a DROP RANGE#L n or ADD RANGE#L n expression without a level number for a multilevel partitioning expression, the partitioning at the default level for the options must not compare character or graphic data. Otherwise, the system returns an error to the requestor.
- If you specify DROP RANGE[#L n] or ADD RANGE[#L n] for a multilevel partitioning expression, the corresponding partitioning expression at the default or specified level of this DROP RANGE[#L n] or ADD RANGE[#L n] must be defined only using a RANGE_N function that does not compare character or graphic. You cannot define the expression with a BLOB, CLOB, or BIGINT data type. Otherwise, the system returns an error to the requestor.
Using Range Expressions
Each range specified for a start_expression or end_expression must conform to the rules of a RANGE_N function (see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for details).
You must obey the following rules.- A range value must be compatible in data type with the test expression that defines the corresponding existing partitioning expression for the table.
- The ranges must be in sequential order and cannot overlap one another.
- The last range of a range expression sequence must specify an ending range and be preceded by the keyword AND.
- Not all data types are not supported for the test expression or in the start and end expressions. See Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for details.
- You can specify either an OR UNKNOWN clause or an UNKNOWN
clause for a range expression sequence, but not both.
If you specify both, the system returns an error to the requestor.
- Dropping a range or partition does not necessarily cause rows to be deleted. Affected rows could be moved to an added range or to the optional NO RANGE [OR UNKNOWN] or [UNKNOWN] or UNKNOWN partitions.
- The resulting new partitioning expression for a level must
define at least one range. Otherwise, the system returns an error to the
requestor.
For multilevel partitioning, the resulting new partitioning expressions must each define at least two partitions. Otherwise, the system returns an error to the requestor.
If a new partitioning expression corresponds to this level … | Then … |
---|---|
1 | the number of partitions defined must be less than or equal to the previously defined maximum. |
2 or higher | it must define the same number of partitions. |
Using ALTER Table Request
An ALTER TABLE request that specifies a DROP RANGE[#L n] expression, an ADD RANGE[#L n] expression, or both, is equivalent to the following generalized ALTER TABLE request syntax expressed in pseudo-BNF format.
ALTER TABLE table_name MODIFY [ [ [NOT] UNIQUE ] PRIMARY INDEX [ index_name | NOT NAMED ] [ (primary_index_column_list) | NO PRIMARY INDEX ] PARTITION BY { new_partitioning_level | (new_partitioning_level [, new_partitioning_level ]...) } [ null_partition_handler ];
- table_name
- index_name
- primary_index_column_list
- new_partitioning_level
- Same as in the original ALTER TABLE request.
- null_partition_handler
- Refers to a WITH INSERT or WITH DELETE clause in the original ALTER TABLE request.