15.00 - Different Multilevel Row Partitioning of the Same Table - Teradata Database

Teradata Database Design

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

Different Multilevel Row Partitioning of the Same Table

The purpose of the following multipart example is to demonstrate the various properties of different multilevel row partitionings of the same data.

Stage 1: First multilevel row partitioning of the orders table.

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

This definition implies the following information about the row partitioning of orders.

  • Number of row partitions in the first level, or highest, level = d1 = 6
  • Number of row partitions in second, or lowest, level = d2 = 11
  • Total number of combined partitions = d1 * d2 = 6 * 11 = 66
  • Combined partitioning expression = (p1 - 1) * d2 + p2 = (p1 - 1) * 11 + p2
  • Now if o_custkey1 is 15 and o_custkey2 is 55, the following additional information is implied:

  • Row partition number for level 1 = PARTITION#L1 = p1(15) = 2.
  • Row partition number for level 2 = PARTITION#L2 = p2(55) = 6.
  • PARTITION#L3 through PARTITION#L62 are all 0.
  • Combined partition number = PARTITION = (2-1)*11 + 6 = 17.
  • The following table indicates the row partition numbers for the various defined ranges for o_custkey1 and o_custkey2.

     

    Value of o_custkey1

     

    Value of o_custkey2

     

    Value of (p1-1)*d2+p2

    PARTITION

    Value of p1

    PARTITION#L1

    Value of p2

    PARTITION#L2

    0 - 9

    0 - 9

                     1

    1

                      1

    10 - 19

                     2

                      2

    20 - 29

                     3

                      3

    30 - 39

                     4

                      4

    40 - 49

                     5

                      5

    50 - 59

                     6

                      6

    60 - 69

                     7

                      7

    70 - 79

                     8

                      8

    80 - 89

                     9

                      9

    90 - 99

                   10

                    10

    100

                   11

                    11

    10 - 19

    0 - 9

                    12

    2

                      1

    10 - 19

                    13

                      2

    20 - 29

                    14

                      3

    30 - 39

                    15

                      4

    40 - 49

                    16

                      5

    50 - 59

                    17

                      6

    60 - 69

                    18

                      7

    70 - 79

                    19

                      8

    80 - 89

                    20

                      9

    90 - 99

                    21

                    10

    100

                    22

                    11

    20 - 29

    0 - 9

                    23

    3

                      1

    10 - 19

                    24

                      2

    20 - 29

                    25

                      3

    30 - 39

                    26

                      4

    40 - 49

                    27

                      5

    50 - 59

                    28

                      6

    60 - 69

                    29

                      7

    70 - 79

                    30

                      8

    80 - 89

                    31

                      9

    90 - 99

                    32

                    10

    100

                    33

                    11

    30 - 39

    0 - 9

                   34

    4

                      1

    10 - 19

                   35

                      2

    20 - 29

                   36

                      3

    30 - 39

                   37

                      4

    40 - 49

                   38

                      5

    50 - 59

                   39

                      6

    60 - 69

                   40

                      7

    70 - 79

                   41

                      8

    80 - 89

                   42

                      9

    90 - 99

                   43

                    10

    100

                   44

                    11

    40 - 49

    0 - 9

                   45

    5

                      1

    10 - 19

                   46

                      2

    20 - 29

                   47

                      3

    30 - 39

                   48

                      4

    40 - 49

                   49

                      5

    50 - 59

                   50

                      6

    60 - 69

                   51

                      7

    70 - 79

                   52

                      8

    80 - 89

                   53

                      9

    90 - 99

                   54

                    10

    100

                   55

                    11

    50

    0 - 9

                   56

    6

                      1

    10 - 19

                   57

                      2

    20 - 29

                   58

                      3

    30 - 39

                   59

                      4

    40 - 49

                   60

                      5

    50 - 59

                   61

                      6

    60 - 69

                   62

                      7

    70 - 79

                   63

                      8

    80 - 89

                   64

                      9

    90 - 99

                   65

                    10

    100

                   66

                    11

    The following case examples demonstrate some of the properties of this multilevel partitioning scheme.

  •      SELECT *
         FROM orders
         WHERE custkey1 = 15;
  • In this case, there might be qualifying rows in the row partitions where the combined row partition numbers = (2 -1)*11 + (1 to 11) = 11 + (1 to 11) = 12 to 22.

  •      SELECT *
         FROM orders
         WHERE (o_custkey1 = 15

         OR     o_custkey1 = 25)
         AND    custkey2 BETWEEN 20 AND 50;
  • In this case, there might be qualifying rows in the row partitions where the combined partition numbers are in (14 to 17, 25 to 28).

  •      SELECT *
         FROM orders     
         WHERE o_custkey2 BETWEEN 42 AND 47;
  • In this case, there might be qualifying rows in the row partitions where the combined partition numbers are in (5, 16, 27, 38, 49, 60).

    Stage 2:

    Suppose you submit the following ALTER TABLE request on orders.

         ALTER TABLE orders
         MODIFY PRIMARY INDEX
           DROP RANGE BETWEEN   0 
                      AND       9 
                      EACH     10 
           ADD RANGE  BETWEEN  51 
                      AND      70 
                      EACH     10,
           DROP RANGE BETWEEN 100 
                      AND     100 
           ADD RANGE -100 TO -2;

    This alters the row partitioning expressions to be the following expressions.

         RANGE_N(o_custkey1 BETWEEN   10 
                            AND       50 
                            EACH      10, 51 AND 70 
                            EACH      10),
         RANGE_N(o_custkey2 BETWEEN -100 
                            AND       -2, 0 
                            AND       99 
                            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_custkey1 INTEGER,
           o_custkey2 INTEGER)
         PRIMARY INDEX (o_orderkey)
         PARTITION BY (RANGE_N(o_custkey1 BETWEEN   10 
                                          AND       50 
                                          EACH      10, /* p1 */
                                                    51 
                                          AND       70 
                                          EACH      10),
                       RANGE_N(o_custkey2 BETWEEN -100 
                                          AND       -2, 0 
                                          AND       99 
                                          EACH      10)) /* p2 */;

    This changes the information implied by the initial table definition about the row partitioning of orders as follows.

  • Number of partitions in the first, and highest, level = d1 = 7
  • Number of partitions in the second, and lowest, level = d2 = 11
  • Total number of combined partitions = d1 * d2 = 7 * 11 = 77
  • Combined partitioning expression = (p1 - 1) * d2 + p2 = (p1 - 1) * 11 + p2
  • Now if o_custkey1 is 15 and o_custkey2 is 55, the following additional information is implied.

  • Partition number for level 1 = PARTITION#L1 = p1(15) = 1.
  • Partition number for level 2 = PARTITION#L2 = p2(55) = 7.
  • PARTITION#L3 through PARTITION#L15 are all 0.
  • Combined partition number = PARTITION = (1-1)*11 + 7 = 7.
  • The following table indicates the row partition numbers for the various defined ranges for o_custkey1 and o_custkey2.

     

     Value of o_custkey1

     Value of o_custkey2

    Value of (p1-1)*d2+p2
            PARTITION

             Value of p1
          PARTITION#L1

             Value of p2
          PARTITION#L2

    10 - 19

            -100  -  -2

                        1

    1

                        1

    0  -  9

                        2

                        2

    10  -  19

                        3

                        3

    20  -  29

                        4

                        4

    30  -  39

                        5

                        5

    40  -  49

                        6

                        6

    50  -  59

                        7

                        7

    60  -  69

                        8

                        8

    70  - 7 9

                        9

                        9

    80  -  89

                      10

                      10

    90  -  99

                      11

                      11

    20 - 29

            -100  -  -2

                      12

    2

                        1

    0 -  9

                      13

                        2

    10 - 19

                      14

                        3

    20 - 29

                      15

                        4

    30 - 39

                      16

                        5

    40 - 49

                      17

                        6

    50 - 59

                      18

                        7

    60 - 69

                      19

                        8

    70 - 79

                      20

                        9

    80 - 89

                      21

                      10

    90 - 99

                      22

                      11

    30 - 39

            -100  -  -2

                      23

    3

                        1

    0 - 9

                      24

                        2

    10 - 19

                      25

                        3

    20 - 29

                      26

                        4

    30 - 39

                      27

                        5

    40 - 49

                      28

                        6

    50 - 59

                      29

                        7

    60 - 69

                      30

                        8

    70 - 79

                      31

                        9

    80 - 89

                      32

                      10

    90 - 99

                      33

                      11

    40 - 49

            -100  -  -2

                      34

    4

                        1

    0 - 9

                      35

                        2

    10 - 19

                      36

                        3

    20 - 29

                      37

                        4

    30 - 39

                      38

                        5

    40 - 49

                      39

                        6

    50 - 59

                      40

                        7

    60 - 69

                      41

                        8

    70 - 79

                      42

                        9

    80 - 89

                      43

                      10

    90 - 99

                      44

                      11

    50

            -100  -  -2

                      45

    5

                        1

    0 - 9

                      46

                        2

    10 - 19

                      47

                        3

    20 - 29

                      48

                        4

    30 - 39

                      49

                        5

    40 - 49

                      50

                        6

    50 - 59

                      51

                        7

    60 - 69

                      52

                        8

    70 - 79

                      53

                        9

    80 - 89

                      54

                      10

    90 - 99

                      55

                      11

    51 - 60

            -100  -  -2

                      56

    6

                        1

    0 - 9

                      57

                        2

    10 - 19

                      58

                        3

    20 - 29

                      59

                        4

    30 - 39

                      60

                        5

    40 - 49

                      61

                        6

    50 - 59

                      62

                        7

    60 - 69

                      63

                        8

    70 - 79

                      64

                        9

    80 - 89

                      65

                      10

    90 - 99

                      66

                      11

    61 - 70

            -100  -  -2

                      67

    7

                        1

    0 - 9

                      68

                        2

    10 - 19

                      69

                        3

    20 - 29

                      70

                        4

    30 - 39

                      71

                        5

    40 - 49

                      72

                        6

    50 - 59

                      73

                        7

    60 - 69

                      74

                        8

    70 - 79

                      75

                        9

    80 - 89

                      76

                      10

    90 - 99

                      77

                      11

    The following cases provide examples of how multilevel row partitioning might be useful.

  •      SELECT *
         FROM orders
         WHERE o_custkey1 = 15;
  • In this case, there might be qualifying rows in the row partitions where the combined partition numbers are in the range (1-1)*11 + (1 TO 11) = 1 TO 11.

  •      SELECT *
         FROM orders
         WHERE (o_custkey1 = 15
         OR     o_custkey1 = 25)
         AND o_custkey2 BETWEEN 20 AND 50;
  • In this case, there might be qualifying rows in the row partitions where the combined partition numbers are in the ranges 4 TO 7 and 15 TO 18.

  •      SELECT *
         FROM orders
         WHERE o_custkey2 BETWEEN 42 AND 47;
  • In this case, there might be qualifying rows in the row partitions where the combined partition number is any of the following.

  •   6
  • 17
  • 28
  • 39
  • 50
  • 61
  • 72