A partitioned table or join index has the following partitioning CHECK constraint.
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 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.
- partitioning_constraint_i
- Each of the partitioning constraints corresponds to a level of partitioning in the order defined for the table or join index.
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 Single-Level Partitioning and for information about the table-level CHECK constraints that Vantage creates for single-level and multilevel partitioned primary index tables and join indexes.