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

prodname
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