## Partitioning CHECK Constraints for Single-Level Partitioning

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 Teradata Vantage™ - Data Dictionary, B035-1092.

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 Teradata Vantage™ - Data Dictionary, B035-1092 for details about DBC.TableConstraints and its role in recording partitioning metadata.

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

## Single-Level Partitioning Example

The following multipart example demonstrates the various properties of different single-level partitioning of the same data.

Stage 1: First single-level partitioning of the orders table.

CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER) PRIMARY INDEX (o_orderkey) PARTITION BY RANGE_N(o_custkey BETWEEN 0 AND 100 EACH 10); /* p1 */

- Number of partitions in the first, and only, level = d1 = 11
- Total number of combined partitions = d1 = 11
- Combined partitioning expression = p1

- Partition number for level 1 = PARTITION#L1 = p1(15) = 2.
- PARTITION#L2 through PARTITION#L15 are all 0.
- Combined partition number = PARTITION = p1(15) = 2.

Value of o_custkey | Result of the RANGE_N function Value of PARTITION Value of PARTITION#L1 |
---|---|

0 - 9 | 1 |

10 - 19 | 2 |

20 - 29 | 3 |

30 - 39 | 4 |

40 - 49 | 5 |

50 - 59 | 6 |

60 - 69 | 7 |

70 - 79 | 8 |

80 - 89 | 9 |

90 - 99 | 10 |

100 | 11 |

Stage 2: Second single-level partitioning of the orders table.

Suppose you then submit the following ALTER TABLE request on orders:

ALTER TABLE orders MODIFY PRIMARY INDEX DROP RANGE BETWEEN 0 AND 9 EACH 10;

This alters the partitioning expression to:

RANGE_N(o_custkey BETWEEN 10 AND 100 EACH 10);

In other words, the table definition after you have performed the ALTER TABLE request is as follows:

CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER) PRIMARY INDEX (o_orderkey) PARTITION BY RANGE_N(o_custkey BETWEEN 10 AND 100 EACH 10); /* p1 */

- Number of partitions in the first, and only, level = d1 = 10
- Total number of combined partitions = d1 = 10
- Combined partitioning expression = p1

- Partition number for level 1 = PARTITION#L1 = p1(15) = 1.
- PARTITION#L2 through PARTITION#L15 are all 0.
- Combined partition number = PARTITION = p1(15) = 1.

The following table indicates the new partition numbers for the various defined ranges for o_custkey:

Value of o_custkey | Result of the new RANGE_N function Value of PARTITION Value of PARTITION#L1 |
---|---|

10 - 19 | 1 |

20 - 29 | 2 |

30 - 39 | 3 |

40 - 49 | 4 |

50 - 59 | 5 |

60 - 69 | 6 |

70 - 79 | 7 |

80 - 89 | 8 |

90 - 99 | 9 |

100 | 10 |

Stage 3: Third single-level partitioning of the orders table.

Suppose you submit the following ALTER TABLE request on orders:

ALTER TABLE orders MODIFY PRIMARY INDEX ADD RANGE BETWEEN 5 AND 9 EACH 1;

This alters the partitioning expression to:

RANGE_N(o_custkey BETWEEN 5 AND 9 EACH 1, 10 AND 100 EACH 10);

In other words, the table definition after you have performed the ALTER TABLE request is as follows:

CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER) PRIMARY INDEX (o_orderkey) PARTITION BY RANGE_N(o_custkey BETWEEN 5 AND 9 EACH 1, 10 AND 100 EACH 10); /* p1 */

- Number of partitions in the first, and only, level = d1 = 15
- Total number of combined partitions = d1 = 15
- Combined partitioning expression = p1

- Partition number for level 1 = PARTITION#L1 = p1(15) = 6.
- PARTITION#L2 through PARTITION#L15 are all 0.
- Combined partition number = PARTITION = p1(15) = 6.

The following table indicates the new partition numbers for the various defined ranges for o_custkey.

Value of o_custkey | Result of the new RANGE_N function Value of PARTITION Value of PARTITION#L1 |
---|---|

5 | 1 |

6 | 2 |

7 | 3 |

8 | 4 |

9 | 5 |

10 - 19 | 6 |

20 - 29 | 7 |

30 - 39 | 8 |

40 - 49 | 9 |

50 - 59 | 10 |

60 - 69 | 11 |

70 - 79 | 12 |

80 - 89 | 13 |

90 - 99 | 14 |

100 | 15 |

Note that this table describes the PARTITION#L n values for a table having a 2-byte ROWID. If the table had an 8-byte ROWID, there would be as many as 62 partitions.

Stage 4: Fourth single-level partitioning of the orders table.

Suppose you submit the following ALTER TABLE request on orders:

ALTER TABLE orders MODIFY PRIMARY INDEX ADD RANGE BETWEEN 0 AND 4 EACH 2;This alters the partitioning expression to be:

RANGE_N(o_custkey BETWEEN 0 AND 4 EACH 2, 5 AND 9 EACH 1, 10 AND 100 EACH 10);

In other words, the table definition after you have performed the ALTER TABLE request is as follows:

CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER) PRIMARY INDEX (o_orderkey) PARTITION BY RANGE_N(o_custkey BETWEEN 0 AND 4 EACH 2, 5 AND 9 EACH 1, 10 AND 100 EACH 10);

- Number of partitions in the first, and only, level = d1 = 18
- Total number of combined partitions = d1 = 18
- Combined partitioning expression = p1

- Partition number for level 1 = PARTITION#L1 = p1(15) = 9.
- PARTITION#L2 through PARTITION#L15 are all 0.
- Combined partition number = PARTITION = p1(15) = 9.

The following table indicates the new partition numbers for the various defined ranges for o_custkey.

Value of o_custkey | Result of the new RANGE_N function Value of PARTITION Value of PARTITION#L1 |
---|---|

0 - 1 | 1 |

2 - 3 | 2 |

4 | 3 |

5 | 4 |

6 | 5 |

7 | 6 |

8 | 7 |

9 | 8 |

10 - 19 | 9 |

20 - 29 | 10 |

30 - 39 | 11 |

40 - 49 | 12 |

50 - 59 | 13 |

60 - 69 | 14 |

70 - 79 | 15 |

80 - 89 | 16 |

90 - 99 | 17 |

100 | 18 |