Single-Level Partitioning - Teradata Database

Single‑Level Partitioning

Partitioning CHECK Constraints for Single‑Level Partitioning

Teradata Database derives a table‑level partitioning CHECK constraint from the partitioning expression. The text for this derived partitioning constraint cannot exceed 16,000 characters; otherwise, Teradata Database aborts the request and returns an error to the requestor.

The following diagrams provides the two forms of this partitioning CHECK constraint derived for single‑level partitioning. The forms differ depending on whether the partitioning expression has an INTEGER data type or not.

The first form applies to partitioning expressions that do not have an INTEGER type. Call this partitioning constraint form 1.

  CHECK ((CAST((partitioning_expression) AS INTEGER)) BETWEEN 1 AND max)

The second form applies to partitioning expressions that have an INTEGER type. Call this partitioning constraint form 2.

  CHECK ((partitioning_expression) BETWEEN 1 AND max)

where:

Syntax element …	Specifies the …
partitioning_expression	partition number returned by the single‑level partitioning expression.
max	maximum number of partitions defined by partitioning_expression. If the partitioning expression is defined using something other than a RANGE_N or CASE_N function, the value of max is 65535. If the partitioning expression is defined using a RANGE_N function, the value of max is the number of partitions defined by the RANGE_N function. If the partitioning expression is defined using a CASE_N function, the value of max is the number of partitions defined by the CASE_N function.

Multilevel‑partitioned tables have a different table‑level partitioning CHECK constraint (see “Multilevel Partitioning” on page 319).

If any one of the following items is true, Teradata Database uses a different form of partitioning constraint:

A partitioning expression for one or more levels consists solely of a RANGE_N function with a BIGINT data type.

An ADD clause is specified for one or more partitioning levels.

The table has 8-byte partitioning.

There is a column-partitioning level.

For other than level 1 of a populated table, the number of partitions for at least one level changes when the table is altered.

For other than level 1 of an empty table, the number of partitions for at least one level decreases when the table is altered.

The cost profile constant PartitioningConstraintForm is set to 1.

The format for this partitioning constraint text is as follows. Call this partitioning constraint form 4.

     CHECK (/*nn bb cc*/ partitioning_constraint_1 …

                    [AND partitioning_constraint_n])

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 = 2. For 8‑byte partitioning, bb = 8.
cc	the column partitioning level. If there is no column partitioning, cc = 00. If there is column partitioning, cc ranges between 01 and nn, inclusive.
partitioning_expression_i	the partitioning expression at partitioning level i.
partitioning_constraint_i	partitioning_expression_i /*i d+a*/ IS NOT NULL if there is row partitioning at partitioning level i. PARTITION#Li /*i d+a*/ = 1 if there is column partitioning at partitioning level i.
i	a partitioning level that ranges between 1 and nn, inclusive. Leading zeros are not used for the value of i.
d	the number of currently defined partitions for the level. For a column‑partitioned level, this includes the 2 internal column partitions. Leading zeros are not used for the value of d.
a	the number of additional partitions that could be added (which might be 0) or X. X occurs for level 2 and higher if this 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 new constraint form would be used regardless of the setting of PartitioningConstraintForm or this is for level 1, a is an integer number. Leading zeros are not used for the value of a.

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

The TableCheck column of DBC.TableConstraints contains the unresolved condition text for a table-level CHECK constraint check or implicit table-level constraint such as a partitioning constraint. The ConstraintType code for such a partitioning constraint is Q for a partitioned object. See Data Dictionary for details.

Rows that violate this partitioning CHECK constraint, including those whose partitioning expression evaluates to null, are not allowed in the table.

Assume you have created 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_orderpriority CHARACTER(21),

       o_clerk         CHARACTER(16),

       o_shippriority  INTEGER,

       o_comment       VARCHAR(79))

     PRIMARY INDEX (o_orderkey)

     PARTITION BY (RANGE_N(o_custkey   BETWEEN 0

                                       AND 49999 

                                       EACH  100))

     UNIQUE INDEX (o_orderkey);

The partitioning CHECK constraint SQL text that is stored in DBC.TableConstraints for this multilevel partitioned primary index is as follows.

     CHECK(RANGE_N(o_custkey BETWEEN 0 

                             AND 49999 

                             EACH  100) 

     BETWEEN 1 AND 500

Now suppose that you create a column‑partitioned version of the orders table, orders_cp, with the following definition.

     CREATE TABLE orders_cp (

       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 partitioning CHECK constraint for this table with 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 could use the following SELECT 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 1,2;

See Data Dictionary for details about DBC.TableConstraints and its role in recording partitioning metadata.

The maximum size of all partitioning CHECK constraints is 16,000 characters.