15.00 - Single-Level Partitioning Example - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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#Ln 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