Vantage derives a table-level partitioning CHECK constraint from the partitioning expression. The text for this derived partitioning constraint cannot exceed 16,000 characters; otherwise, the system 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)
- partitioning_expression
- Partition number returned by the single-level partitioning expression.
- max
- Maximum number of partitions defined by partitioning_expression. If partitioning_expression is defined by the RANGE_N or CASE_N function, max is the number of partitions defined by the RANGE_N or CASE_N function. Otherwise, max is 65535.
Multilevel-partitioned tables have a different table-level partitioning CHECK constraint (see Multilevel Partitioning).
- 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 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 ])
- 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, where i is in [1, nn]. Leading zeros are not used for the value of i.
- partitioning_constraint_i
- partitioning_constraint_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.
- partitioning_constraint_i /*i d+a*/ IS NOT NULL
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. For more information, see ConstraintType Column.
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 may 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;
The maximum size of all partitioning CHECK constraints is 16,000 characters.