15.00 - Modifying the Partitioning of a Table Using the ADD RANGE and DROP RANGE Options - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Modifying the Partitioning of a Table Using the ADD RANGE and DROP RANGE Options

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 (see “Rules for Modifying a Character Partitioning Expression” on page 121 for additional rules that apply to character partitioning expressions).

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.

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 (see “Rules for Modifying Populated and Unpopulated Tables and Join Indexes” on page 89) and you must use the PARTITION BY clause to specify the desired partitioning levels and expressions.

  • Two general guidelines for adding and dropping ranges.
  • Do not overlap new ranges with dropped ranges. For example, do not do either of the following things.
  • DROP 2 ranges and ADD one range that covers both of the original 2 ranges.
  • 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.
  • The reasons why this is true are as follows.

  • 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.
  • 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.
  • If you truly want 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.

  • You cannot use an ALTER TABLE … ADD RANGE[#Ln] or an ALTER TABLE … DROP RANGE[#Ln] request to alter the partitioning of a table between being partitioned and being unpartitioned.
  • 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” on page 89).
  • 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 SQL Data Manipulation Language for information about the INSERT … SELECT and MERGE statements and their LOGGING ERRORS option, and “CREATE ERROR TABLE” on page 228 for information about error tables.

  • If you specify NO PRIMARY INDEX without altering partitioning and the table currently has no primary index, the system returns an error to the requestor.
  • If you specify NO PRIMARY INDEX with NOT PARTITIONED and the table currently neither has a primary index nor is partitioned, the system returns an error to the requestor.
  • If you specify NO PRIMARY INDEX without altering partitioning and the table currently has a partitioned primary index, the system returns an error to the requestor.
  • If you specify NO PRIMARY INDEX with a DROP RANGE#n clause, an ADD RANGE#n clause, or both, and the table currently has a partitioned primary index, the system returns an error to the requestor.
  • 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[#Ln] and ADD RANGE[#Ln] 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 Index or Partitioning for a Table” on page 82, “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90 and “ALTER TABLE” in SQL Data Definition Language Syntax and Examples).

  • 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” on page 89).
  • If you specify DROP RANGE without a partitioning level number in the first alter partitioning expression, it is equivalent to DROP RANGE#Ln 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.

  • If you specify DROP RANGE without specifying a level number in other than the first alter partitioning expression, DROP RANGE is equivalent to DROP RANGE#Ln, 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.

  • If you specify DROP RANGE[#Ln] 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.
  • Otherwise, the system returns an error to the requestor.

  • The default or specified level for DROP RANGE#Ln, the value for n must not exceed the number of partitioning levels currently defined for the table.
  • Otherwise, the system returns an error to the requestor.

  • If you specify DROP RANGE#Ln or ADD RANGE#Ln, 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 an ADD RANGE expression without also specifying a level in the same ALTER TABLE request as a DROP RANGE[#Ln] expression in an alter partitioning expression, the operation is equivalent to an ADD RANGE#Ln expression, where the value for n is the same as the default or specified level number of that DROP RANGE[#Ln].
  • If you specify an ADD RANGE expression without a level and without a DROP RANGE[#Ln] expression in the first alter partitioning expression for a multilevel partitioned table, that ADD RANGE expression is equivalent to ADD RANGE#Ln, 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.

  • If you specify an ADD RANGE expression without specifying a level and without also specifying a DROP RANGE[#Ln] expression in other than the first alter partitioning expression for a multilevel partitioned table, that ADD RANGE expression is equivalent to ADD RANGE#Ln, 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.

  • If you specify an ADD RANGE expression with a DROP RANGE[#Ln] in an alter partitioning expression, it is equivalent to ADD RANGE[#Ln], where n is the same as the default or specified level of that DROP RANGE[#Ln].
  • If you specify an ADD RANGE[#Ln] clause with a DROP RANGE[#Ln] 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[#Ln] expression.
  • Otherwise, the system returns an error to the requestor.

  • If you specify an ADD RANGE clause without a DROP RANGE[#Ln] in the first alter partitioning expression of a multilevel partitioning expression, it is equivalent to ADD RANGE[#Ln], where n is equal to the number of the first partitioning level that is defined solely with a RANGE_N partitioning expression.
  • Otherwise, the expression is equivalent to ADD RANGE[#Ln], 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.

  • If you specify an ADD RANGE[#Ln] expression without also specifying a DROP RANGE[#Ln] 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.
  • Otherwise, the system returns an error to the requestor.

  • If you specify a DROP RANGE#Ln or ADD RANGE#Ln 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#Ln or ADD RANGE#Ln 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 an ADD RANGE[#Ln] expression for a multilevel partitioning expression, the value for n must not exceed the number of partitioning levels currently defined for the table.
  • Otherwise, the system returns an error to the requestor.

  • If you specify a WITH DELETE clause for a multilevel partitioning expression, there must be a DROP RANGE[#Ln] in at least one of the alter partitioning expressions.
  • Otherwise, the system returns an error to the requestor.

  • If you specify a WITH INSERT clause for a multilevel partitioning expression, there must be a DROP RANGE[#Ln] in at least one of the alter partitioning expressions.
  • Otherwise, the system returns an error to the requestor.

  • For 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.
  • Otherwise, the system returns an error to the requestor.

  • If 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.
  • Otherwise, the system returns an error to the requestor.

  • For both multilevel row‑partitioned tables and multilevel column‑partitioned tables, a DROP RANGE WHERE partition_conditional_expression specification must reference the system‑derived column PARTITION#Ln, 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[#Ln] and no other columns.
  • Otherwise, the system returns an error to the requestor.

  • For both multilevel row‑partitioned tables and multilevel column‑partitioned tables, if the table definition specifies an explicit column named PARTITION#L1, you cannot specify PARTITION#L1 in the DROP RANGE WHERE partition_conditional_expression specification of a DROP RANGE[#Ln] clause.
  • Otherwise, the system returns an error to the requestor.

  • If you specify DROP RANGE[#Ln] or ADD RANGE[#Ln] for a multilevel partitioning expression, the corresponding partitioning expression at the default or specified level of this DROP RANGE[#Ln] or ADD RANGE[#Ln] 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.

  • Each range specified for a start_expression or end_expression must conform to the rules of a RANGE_N function (see SQL Functions, Operators, Expressions, and Predicates 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 SQL Functions, Operators, Expressions, and Predicates 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 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.

  • An ALTER TABLE request that specifies a DROP RANGE[#Ln] expression, an ADD RANGE[#Ln] 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 specify DROP RANGE[#Ln] WHERE partition_conditional_expression for that level, then the new partitioning expression for that level does not include the specified existing ranges or partitions where the partition conditional expression evaluates to TRUE.
  • If you specify DROP RANGE[#Ln] alter_ranges for that level, then the new partitioning expression for that level does not include the specified ranges and partitions in the alter ranges.
  • If you specify ADD RANGE[#Ln] alter_ranges for that level, then 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[#Ln] clause if one is specified.
  • If you 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.
  • If you 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.
  • If you 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.
  • If you 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.
  • If you 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.
  • If you 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.
  • If you 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.
  • If you 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.

  • The new partitioning expression for a level must specify at least one range.
  • Otherwise, 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.