Single-Level Partitioning | Database Design | Teradata Vantage - 17.10 - Single-Level Partitioning - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

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).

If any one of the following items is true, Vantage uses a different form of partitioning constraint:
  • 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.

The partitioning constraint at partitioning level i, where i is in [1, nn]. Leading zeros are not used for the value of i.
d is the number of currently defined partitions for the level. For a column-partitioned level, this includes the 2 internal column partitions. Leading zeros are not used for the value of d.
a is the number of additional partitions that could be added.Leading zeros are not used for the value of a.

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 */
This definition implies the following information about the partitioning of orders:
  • Number of partitions in the first, and only, level = d1 = 11
  • Total number of combined partitions = d1 = 11
  • Combined partitioning expression = p1
If the value of o_custkey is 15, then the following additional information is implied:
  • 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 */
This changes the information implied by the initial table definition about the partitioning of orders as follows:
  • Number of partitions in the first, and only, level = d1 = 10
  • Total number of combined partitions = d1 = 10
  • Combined partitioning expression = p1
Now if o_custkey is 15, the following additional information is implied:
  • 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 */
This changes the information implied by the second table definition about the partitioning of orders as follows:
  • Number of partitions in the first, and only, level = d1 = 15
  • Total number of combined partitions = d1 = 15
  • Combined partitioning expression = p1
Now if o_custkey is 15, the following additional information is implied:
  • 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);
This changes the information implied by the third table definition about the partitioning of orders as follows:
  • Number of partitions in the first, and only, level = d1 = 18
  • Total number of combined partitions = d1 = 18
  • Combined partitioning expression = p1
Now if o_custkey is 15, the following additional information is implied.
  • 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