Modifying Partitioning Using the ADD RANGE and DROP RANGE Options - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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.

Rows can be moved from their current partition to any of the following other partitions.
  • 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.

There are two general guidelines for adding and dropping ranges:
  • 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), and Modifying the Partitioning of a Table or Join Index “ALTER TABLE” in 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

The following conditions are true if you specify 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.

    Teradata Database 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

The following rules are true for 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];
where:
  • table_name, null_partition_handler, and other options are the same as for the original ALTER TABLE request.
  • null_partition_handler refers to a WITH INSERT | DELETE clause.
  • The number of new partitioning levels is the same as the number of existing partitioning levels for the table.
  • Each new partitioning level is the same as the existing partitioning for that level except as noted in the following points.
If you... ...Then Otherwise,
specify DROP RANGE[#L n] WHERE partition_conditional_expression for that level, the new partitioning expression for that level does not include the specified existing ranges or partitions where the partition conditional expression evaluates to TRUE.  
specify DROP RANGE[#L n] alter_ranges for that level, the new partitioning expression for that level does not include the specified ranges and partitions in the alter ranges.  
specify ADD RANGE[#L n] alter_ranges for that level, the new partitioning expression for that level includes the addition of the new ranges and partitions merged in order with the existing ranges after first applying the DROP RANGE[#L n] clause if one is specified.  
reduce the number of defined partitions for a level to 0 after dropping and adding any partitions, the system returns an error to the requestor.  
reduce the number of defined partitions for a level by r partitions after dropping partitions, adding partitions, or both, and the level previously specified an ADD option, Teradata Database increases the value for ADD by r.  
reduce the number of defined partitions for a level by r partitions after dropping partitions, adding partitions, or both, and the level did not previously specify an ADD option, Teradata Database adds an ADD r specification for the level.  
increase the number of defined partitions for a level by i partitions after dropping partitions, adding partitions, or both, and the level previously specified an ADD option with a value greater than i, Teradata Database decreases the specified value for ADD by i.  
increase the number of defined partitions for a level by i partitions after dropping partitions, adding partitions, or both, and the level previously specified an ADD option with a value equal to i, Teradata Database removes the ADD option for that level.  
increase the number of defined partitions for a level by i partitions after dropping partitions, adding partitions, or both, and the level previously specified an ADD option with a value less than i either explicitly or by default, the system returns an error to the requestor.  

increase the number of defined partitions for a level by i partitions after dropping partitions, adding partitions, or both, and the level did not previously specify an ADD option either explicitly or by default, but one or more partitioning levels do specify an ADD option either explicitly or by default,

the system returns an error to the requestor.  
increase the number of defined partitions in a multilevel partitioning expression for a level for other than level 1 by i partitions after dropping partitions, adding partitions, or both, none of the levels specifies an ADD option, and the table is populated with rows, the system returns an error to the requestor.
You can only perform this operation on an unpopulated table.
 
use a new partitioning expression, the new partitioning expression for a level must specify at least one range. the system returns an error to the requestor.
This error occurs under the following circumstances:
  • When only one partition is defined for a row partitioning level with an ADD 0 or with no ADD specification

    AND

  • The maximum number of partitions is not increased to the largest value that would not increase the partitioning from 2-byte partitioning to 8-byte partitioning

    OR

  • If the table or join index already has 8-byte partitioning, the maximum number of partitions is not increased to the largest value that does not cause the maximum combined partition number to exceed 9,223,372,036,854, 775,807

    AND

  • The maximum is not increased to at least 2.