Partitioning CHECK Constraint for Partitioned Tables With 8‑Byte Partitioning
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. Otherwise, the system returns an error to the requestor.
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.
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.
Syntax element …
the number of partitioning levels.
the number of bytes used to store the internal partition number in the row header.
the column partitioning level for a column‑partitioned table.
the partitioning expression at partitioning level i.
The value ranges between 1 and nn, inclusive.
Teradata Database does not store leading zeros for the value of 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.
X occurs for levels 2 and higher if this partitioning CHECK constraint form is only being used because the cost profile constant PartitioningConstraintForm is set to 1 to force use of the this partitioning CHECK constraint form in all cases.
If Teradata Database would use this form of partitioning CHECK constraint regardless of the setting of PartitioningConstraintForm, 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)” on page 31 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_orderstatus CHARACTER(1) CASESPECIFIC,
o_totalprice DECIMAL(13,2) NOT NULL,
o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
NO PRIMARY INDEX
PARTITION BY (RANGE_N(o_custkey BETWEEN 0
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
IS NOT NULL AND PARTITION#L2 /*2 9+10*/=1)
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’)
WHERE ConstraintType = 'Q'
AND ColumnPartitioningLevel >= 1
ORDER BY DBaseId, TVMId;
See “Rules and Usage Notes for Partitioned Tables” on page 625 and “Restrictions for Multilevel Partitioning” on page 634 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.