Stage 2: Altering the row partitioning expressions - Teradata VantageCloud Lake

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)

That is, 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 */;

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

SELECT * FROM orders WHERE o_custkey1 = 15;

There may 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;

There may 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;