16.20 - Partitioning CHECK Constraint for Partitioned Tables With 8-Byte Partitioning - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

Just as it does for a 2-byte partitioned primary index, Teradata Database derives a partitioning CHECK constraint for 8-byte partitioning from the partitioning expressions.

The text for this CHECK constraint that is derived for table constraints, partitioning constraints, and named constraints, cannot exceed 16,000 characters.

Teradata Database uses this form of partitioning CHECK constraint when any one of the following things is true about the partitioning expression for a table or join index.
  • A partitioning expression for at least 1 level is defined only by a RANGE_N function with a BIGINT data type.
  • At least 1 partitioning level in the partitioning expression specifies an ADD clause.
  • The table or join index has 8-byte partitioning.
  • The table or join index is column-partitioned.
  • The number of partitions for at least 1 partitioning level other than level 1 of a populated table changes when the table is altered with an ALTER TABLE statement.
  • The number of partitions for at least 1 partitioning level other than level 1 of an unpopulated table decreases when the table is altered with an ALTER TABLE statement.

The format for this partitioning CHECK constraint text for multilevel partitioning with 8-byte partitioning, which applies to both row-partitioned and column-partitioned tables and join indexes, is as follows.



where n is the number of the highest partitioning level defined for the partitioning expression. For example, if the partitioning expression for a table has 18 partitioning levels, then the value for n is 18, and the partitioning CHECK constraint documents 18 partitioning expressions.

Each of the individual partitioning constraints in the partitioning CHECK constraint corresponds to a level of partitioning in the order defined for the table.

where:

Syntax element … Specifies …
nn the number of partitioning levels.
  • For 2-byte partitioning, nn ranges between 01 and 15, inclusive.
  • For 8-byte partitioning, nn ranges between 01 and 62, inclusive.
bb the number of bytes used to store the internal partition number in the row header.
  • For 2-byte partitioning, bb = 02.
  • For 8-byte partitioning, bb = 08.
cc the column partitioning level for a column-partitioned table.
  • If the table is not column-partitioned, cc = 00.
  • If the table is column-partitioned, the value of cc ranges between 01 and nn, inclusive, where nn is the number of partitioning levels for the table.
partitioning_constraint_i the partitioning expression at partitioning level i.
  • If the table or join index is row-partitioned at partitioning level i, the form of partitioning_constraint_i is as follows.

    partitioning_expression_i /*i d+a*/ IS NOT NULL

  • If the table or join index is column-partitioned at partitioning level i, the form of partitioning_constraint_i is as follows.

    PARTITION#Li /*i d+a*/=1

where:

  • partitioning_expression_i is the partitioning expression at level i.
  • i is the partitioning level of the partitioning expression for the table or join index.

    The value ranges between 1 and nn, inclusive.

    Teradata Database does not store leading zeros for the value of i.

  • d is the number of defined partitions for partitioning level i.

    For a column partitioning level, this value includes the 2 column partitions reserved for internal use.

    Teradata Database does not store leading zeros for the value of d.

  • a is the number of partitions that could be added to the partitioning level (which might be 0).

    If Teradata Database uses this form of partitioning CHECK constraint, or if the partitioning level is level 1, the value of a is an INTEGER number.

    Teradata Database does not store leading zeros for the value of a.

See ALTER TABLE (Basic Table Parameters) for more information about the ALTER TABLE statement.

The following example demonstrates the form of partitioning CHECK constraint that Teradata Database uses for 8-byte partitioning. Assume you create the following table.

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_comment       VARCHAR(79))
     NO PRIMARY INDEX
     PARTITION BY (RANGE_N(o_custkey BETWEEN  0
                                     AND 100000
                                     EACH     1),
                   COLUMN)
     UNIQUE INDEX (o_orderkey);

The product of the maximum partition number of each partitioning level is 100,000 (100,000 * 1), which is greater than 65,535, so the table has 8-byte partitioning. The table-level partitioning CHECK constraint text for this 8-byte partitioning is as follows.

     CHECK (/*02 08 02*/ RANGE_N(o_custkey BETWEEN  0
                                           AND 100000
                                           EACH     1
            /*1 100001+485440633518572409*/
            IS NOT NULL AND PARTITION#L2 /*2 9+10*/=1)

You can use the following SELECT statement to retrieve the level for the column partitioning for each of the objects that have column partitioning in the system.

     SELECT DBaseId, TVMId, ColumnPartitioningLevel
            (TITLE ‘Column Partitioning Level’)
     FROM DBC.TableConstraints
     WHERE ConstraintType = 'Q'
     AND   ColumnPartitioningLevel >= 1
     ORDER BY DBaseId, TVMId;

See Rules and Usage Notes for Partitioned Tables and Restrictions for Multilevel Partitioning for information about the table-level partitioning CHECK constraints that Teradata Database creates for single-level and multilevel partitioning tables and join indexes with 2-byte partitioning.