15.00 - Rules for the MODIFY, MODIFY PRIMARY INDEX, and MODIFY Partitioning Option For Multilevel Partitioning - 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)

Rules for the MODIFY, MODIFY PRIMARY INDEX, and MODIFY Partitioning Option For Multilevel Partitioning

The general rules for MODIFY and MODIFY PRIMARY INDEX] (see “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85) also apply to the multilevel case with the following exception.

  • If you specify PARTITION BY, the system accepts the request and alters the table to have a partitioning with the specified new partitioning expressions as long as one of the following is true.
  • The table is empty.
  • The new partitioning expressions are defined by DROP RANGE[#Ln] and ADD RANGE[#Ln] options that do not drop ranges other than from the beginning or end of the ranges, with at least one remaining range, and then does not add ranges between the resulting beginning and end of the ranges.
  • In addition, if the table has multilevel partitioning and the modification of a partitioning expression includes dropping the NO RANGE [OR UNKNOWN] or UNKNOWN partitions, that partitioning expression must not have previously been modified or has only been modified such that dropping the RANGE[OR UNKNOWN] or UNKNOWN partitions would not cause an internal partition number to be skipped in the sequence of internal partition numbers for the ranges.

    For levels other than the first, the total number of partitions for each level must not change. This means that the number of partitions dropped must match the number of partitions added in the ALTER TABLE request for that level.

    Any partitions that are not explicitly allocated to other partitions in the multilevel partitioning for a table or join index are reserved for level 1.

    The maximum number of partitions for level 1 for 2‑byte partitioning is the following.

    The maximum number of partitions for level 1 for 8‑byte partitioning is the following.

    where:

     

    Equation element …

    Specifies …

    the FLOOR, or lower truncated value, of the expression . See SQL Functions, Operators, Expressions, and Predicates for information about how Teradata implements the FLOOR function.

    di

    the number of partitions at level i.

    You can alter level 1 to have between 1 (for single‑level partitioning) or 2 (for multilevel partitioning) and this maximum number of partitions.

    Otherwise, Teradata Database aborts the request and returns an error message to the requestor.

  • Teradata Database derives a new table-level partitioning CHECK constraint from a new set of partitioning expressions for a table or join index and replaces the previous partitioning CHECK constraint.
  • When you alter the partitioning of a partitioned database object, Teradata Database derives a new partitioning CHECK constraint from the new set of partitioning expressions for each partitioned table and replaces the previous partitioning CHECK constraint. See “Partitioning CHECK Constraints for Partitioned Tables With 2‑Byte Partitioning” on page 630 and “Partitioning CHECK Constraint for Partitioned Tables With 8‑Byte Partitioning” on page 632 for more information about the partitioning CHECK constraints that Teradata Database derives for row‑partitioned and column‑partitioned tables and join indexes, respectively.
  • If an existing row violates the new partitioning expressions and you have not specified a WITH DELETE or WITH INSERT clause, then you cannot apply the new partitioning.

  • If an existing row violates a new partitioning expression and you have not specified a WITH DELETE or WITH INSERT [INTO] save_table null partition handler clause, then you cannot apply the new partitioning expression.
  • Rows that violate the implied partitioning CHECK constraint, including those whose partitioning expression evaluates to null, are not allowed in the table.

  • For an unpartitioned primary‑indexed table, the condition PARTITION <> 0 should always evaluate to FALSE for all rows in the table; however, the Optimizer does not make this assumption, which enables the condition to be validated.
  • For example, you can use the following query to select rows where PARTITION <> 0.

         SELECT * 
         FROM nppi_table_name 
         WHERE PARTITION <> 0;

    If things are working correctly, the retrieve step could skip reading the rows of the table; however, the retrieve step does read all the rows, which enables the condition to be validated. The query returns a row only if its ROWID value is incorrect or if the process of extracting the partition number from the ROWID is not working correctly.

  • The condition PARTITION#Ln <> 0, where n is greater than the number of levels defined for the table (or the table is an unpartitioned primary‑indexed table) should always evaluate to FALSE for all rows in the table; however, the Optimizer does not make this assumption, which enables the condition to be validated.
  • For example, you can use the following query to select rows where PARTITION#Ln <> 0.

         SELECT * 
         FROM partitioned_table_name 
         WHERE PARTITION#Ln <> 0;

    If things are working correctly, all partitions for the table could be eliminated for the retrieve step; however, the system does not perform row partition elimination for this condition.

    The request returns a row only for the following scenarios.

  • The ROWID value is incorrect
  • The process of extracting the partition number from the ROWID is not working correctly
  • A system-derived column PARTITION#Ln, where the value of n ranges from 1 - 62, inclusive, is equivalent to a value expression where the expression is the same as the partitioning expression at the specified level as defined for the primary index of the table with column references appropriately qualified as needed.
  • The value expression evaluates to 0 for the following cases.

  • There is no partitioning expression for the level
  • The primary index is not partitioned
  • This equivalence is true only if the row specifies the correct internal partition, meaning that the internal partition number is extracted from the row and converted to the external partition number for the corresponding level, rather than actually recalculating it from the partitioning columns of a row.

    A query that selects rows WHERE PARTITION#Ln <> partitioning_expression_n, where the value of n is the same on both sides of the inequality (the value of n must be an integer value that ranges between 1 and 62, inclusive), only returns rows that are not properly partitioned. In other words, the query does not return any rows if things are working correctly.

    If the query returns rows, you must revalidate the table (see “General Rules and Restrictions for the REVALIDATE Option” on page 129).