Vantage derives a table-level partitioning CHECK constraint from the partitioning expression for partitioned tables. The text for this constraint, which is derived for table constraints, partitioning constraints, and named constraints cannot exceed 16,000 characters.
- The first form applies to single-level 2-byte partitioning expressions that do not have an INTEGER data type and are not defined only by a RANGE_N function.
In this case, the type of the expression must be cast to INTEGER.
CHECK ( ( CAST ( partitioning_expression ) AS INTEGER ) ) BETWEEN 1 AND max
- The second form applies to single-level 2-byte partitioning expressions that have an INTEGER data type.
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.
See Rules and Usage Notes for Partitioned Tables and Restrictions for Multilevel Partitioning for information about the table-level partitioning CHECK constraints that Vantage creates for single-level and multilevel partitioned primary index tables and join indexes.
- The third form applies to multilevel 2-byte partitioning expressions.
The following diagram provides the form of this partitioning CHECK constraint that Vantage derives for a multilevel partitioned primary index with 2-byte partitioning.
CHECK ( /* nn */ partitioning_expression_1 IS NOT NULL AND partitioning_expression_2 IS NOT NULL [ partitioning_expression_n IS NOT NULL ] [...] )
- nn
- Number of levels, or number of partitioning expressions, in the multilevel partitioning. nn can range between 02 and 15, inclusive.
- partitioning_expression_1
- The partition number returned by the first multilevel partitioning expression.
- partitioning_expression_2
- The partition number returned by the second multilevel partitioning expression.
- partitioning_expression_n
- The partition number returned by the nth multilevel partitioning expression.
For example, suppose 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_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), RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH)) UNIQUE INDEX (o_orderkey);
The partitioning CHECK constraint SQL text that would be stored in DBC.TableConstraints for this multilevel partitioned primary index with 2-byte partitioning is as follows.
CHECK (/*02*/ RANGE_N(o_custkey BETWEEN 0 AND 49999 EACH 100) IS NOT NULL AND RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH) IS NOT NULL )
You could use the following SELECT statement to retrieve index constraint information for each of the multilevel partitioned objects.
SELECT * FROM DBC.TableConstraints WHERE ConstraintType = 'Q' AND SUBSTRING(TableCheck FROM 1 FOR 13) >= 'CHECK (/*02*/' AND SUBSTRING(TableCheck FROM 1 FOR 13) <= 'CHECK (/*15*/';