16.10 - Single-Level Partitioning - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Partitioning CHECK Constraints for Single-Level Partitioning

Teradata Database derives a table-level partitioning CHECK constraint from the partitioning expression. The text for this derived partitioning constraint cannot exceed 16,000 characters; otherwise, Teradata Database 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)

where:

Syntax element … Specifies the …
partitioning_expression partition number returned by the single-level partitioning expression.
max maximum number of partitions defined by partitioning_expression.
  • If the partitioning expression is defined using something other than a RANGE_N or CASE_N function, the value of max is 65535.
  • If the partitioning expression is defined using a RANGE_N function, the value of max is the number of partitions defined by the RANGE_N function.
  • If the partitioning expression is defined using a CASE_N function, the value of max is the number of partitions defined by the CASE_N function.

Multilevel-partitioned tables have a different table-level partitioning CHECK constraint (see Multilevel Partitioning).

If any one of the following items is true, Teradata Database 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 cost profile constant PartitioningConstraintForm is set to 1.

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

where:

Syntax element … Specifies …
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.
partitioning_constraint_i
  • partitioning_expression_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.

i a partitioning level that ranges between 1 and nn, inclusive.

Leading zeros are not used for the value of i.

d 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 the number of additional partitions that could be added (which might be 0) or X.

X occurs for level 2 and higher if this partitioning constraint form is only being used because the cost profile constant PartitioningConstraintForm is set to 1 to force use of the new constraint form in all cases.

If the new constraint form would be used regardless of the setting of PartitioningConstraintForm or this is for level 1, a is an integer number.

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. See Data Dictionary for details.

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 Data Dictionary 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 = d 1 = 11
  • Total number of combined partitions = d 1 = 11
  • Combined partitioning expression = p 1

If the value of o_custkey is 15, then the following additional information is implied:

  • Partition number for level 1 = PARTITION#L1 = p 1(15) = 2.
  • PARTITION#L2 through PARTITION#L15 are all 0.
  • Combined partition number = PARTITION = p 1(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 = d 1 = 10
  • Total number of combined partitions = d 1 = 10
  • Combined partitioning expression = p 1

Now if o_custkey is 15, the following additional information is implied:

  • Partition number for level 1 = PARTITION#L1 = p 1(15) = 1.
  • PARTITION#L2 through PARTITION#L15 are all 0.
  • Combined partition number = PARTITION = p 1(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 = d 1 = 15
  • Total number of combined partitions = d 1 = 15
  • Combined partitioning expression = p 1

Now if o_custkey is 15, the following additional information is implied:

  • Partition number for level 1 = PARTITION#L1 = p 1(15) = 6.
  • PARTITION#L2 through PARTITION#L15 are all 0.
  • Combined partition number = PARTITION = p 1(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 = d 1 = 18
  • Total number of combined partitions = d 1 = 18
  • Combined partitioning expression = p 1

Now if o_custkey is 15, the following additional information is implied.

  • Partition number for level 1 = PARTITION#L1 = p 1(15) = 9.
  • PARTITION#L2 through PARTITION#L15 are all 0.
  • Combined partition number = PARTITION = p 1(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