15.00 - Partitioning CHECK Constraints - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Partitioning CHECK Constraints

A partitioned table or join index has the following partitioning CHECK constraint.

     CHECK (/*nn bb cc*/ partitioning_constraint_1 
            [AND partitioning_constraint_2] … )

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 type of partitioning.

  • For 2‑byte partitioning, bb is 02.
  • For 8‑byte partitioning, bb is 08.
  • cc

    the column partitioning level.

  • For no column partitioning, cc is 00.
  • Otherwise, cc ranges between 01 and nn, inclusive.
  • Each of the partitioning constraints corresponds to a level of partitioning in the order defined for the table or join index.

     

    The constraint specified by partitioning_constraint_i can be the partitioning constraint for any level and can represent either a row partitioning expression or a column partitioning expression.

    partitioning_constraint_i

    When partitioning_expression_i is the row partitioning expression at level i, and partitioning constraint is the following.

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

    When partitioning_expression_i is the column partitioning expression at level i is the following.

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

    i

    an integer ranging between 1 and nn, inclusive.

    Leading zeros are not specified.

    d

    the number of currently defined partitions for the level.

    Leading zeros are not specified.

    For a column-partitioned level, this includes the two internal column partitions.

    a

    the number of additional partitions that could be added (this can be 0) or x.

    Leading zeros are not specified.

    x occurs for level 2 and higher if the partitioning constraint form is only being used because the cost profile constant PartitioningConstraintForm is set to 1 to force use of the new constraint form in all cases.

    If the constraint form would be used regardless of the setting of PartitioningConstraintForm or this is for level 1, a is an integer number.

    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 table‑level partitioning CHECK constraint SQL 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)
     

    The maximum size of this table‑level CHECK constraint is 16,000 characters.

    You can use the following request 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 “Partitioning CHECK Constraints for Single‑Level Partitioning” on page 362 and “Partitioning CHECK Constraint for Multilevel Partitioning” on page 374 for information about the table‑level CHECK constraints that Teradata Database creates for single‑level and multilevel partitioned primary index tables and join indexes.