17.10 - Example: Modifying Partitioning for Multilevel Partitioned Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Assume you have created a table named orders using the following CREATE TABLE request.

     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)
     PRIMARY INDEX (o_orderkey)
     PARTITION BY (
     RANGE_N(o_custkey   BETWEEN 0 
                         AND 49999 
                         EACH  100),
     RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' 
                         AND     DATE '2006-12-31'
                         EACH INTERVAL '1' MONTH))
     UNIQUE INDEX (o_orderkey);

This table has 2-byte partitioning. If orders is empty, you could submit the following ALTER TABLE MODIFY request to modify it to have a single-level row-partitioned primary index:

     ALTER TABLE orders 
       MODIFY
       PARTITION BY RANGE_N(
                    o_orderdate BETWEEN DATE '2000-01-01' 
                                AND     DATE '2006-12-31' 
                                EACH INTERVAL '1' MONTH);

Assume the previously defined CREATE TABLE definition for orders. If there are no rows with o_orderdate between the dates 2000-01-01 and 2000-12-31, you could submit any one of the following equivalent ALTER TABLE requests to modify the partitioning expression for level 2.

     ALTER TABLE orders 
       MODIFY
       DROP RANGE#L2  BETWEEN DATE '2000-01-01' 
                      AND     DATE '2000-12-31'
       ADD RANGE      BETWEEN DATE '2007-01-01' 
                      AND     DATE '2007-12-31' 
                      EACH INTERVAL '1' MONTH;

or

     ALTER TABLE orders 
       MODIFY
       DROP RANGE#L2 
       WHERE orders.PARTITION#L2 BETWEEN 1 
                                 AND    12
       ADD RANGE#L2              BETWEEN DATE '2007-01-01' 
                                 AND     DATE '2007-12-31' 
                                 EACH INTERVAL '1' MONTH;

or

     ALTER TABLE orders 
       MODIFY
       DROP RANGE#L2 
       WHERE PARTITION#L2 IN (1,2,3,4,5,6,7,8,9,10,11,12)
       ADD RANGE BETWEEN DATE '2007-01-01' 
                 AND     DATE '2007-12-31' 
                 EACH INTERVAL '1' MONTH;

Assume the previously defined CREATE TABLE definition for orders. If there are zero, one, or more rows with o_orderdate between 2000-01-01 and 2000-12-31, you could submit the following ALTER TABLE MODIFY request to alter the row partitioning. In this case, the system deletes the rows from the dropped ranges.

     ALTER TABLE orders 
       MODIFY
       DROP RANGE#L2  BETWEEN DATE '2000-01-01' 
                      AND     DATE '2000-12-31'
       ADD RANGE      BETWEEN DATE '2007-01-01' 
                      AND     DATE '2007-12-31' 
                      EACH INTERVAL '1' MONTH
       WITH DELETE;

Assume you created one table using the previously defined CREATE TABLE definition for orders and another table using the following CREATE TABLE request.

     CREATE TABLE old_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)
     UNIQUE PRIMARY INDEX (o_orderkey);

The following ALTER TABLE MODIFY request is one way to alter the partitioning if there are zero, one, or more rows with o_orderdate between 2000-01-01 and 2000-12-31. In this case, the system saves the rows in old_orders prior to deleting them from orders.

     ALTER TABLE orders 
       MODIFY
       DROP RANGE#L2  BETWEEN DATE '2000-01-01' 
                      AND     DATE '2000-12-31'
       ADD RANGE#L2   BETWEEN DATE '2007-01-01' 
                      AND     DATE '2007-12-31' 
                      EACH INTERVAL '1' MONTH
       WITH INSERT INTO old_orders;

The following ALTER TABLE MODIFY request is not valid for a populated table because adding more partitions to level 2 exceeds the maximum defined for level 2. This is because there was no ADD option for level 2, and all the excess combined partitions were assigned to level 1 as its default ADD option.

     ALTER TABLE orders
       MODIFY
       DROP RANGE#L2  BETWEEN DATE '2000-01-01' 
                      AND     DATE '2000-12-31'
       ADD RANGE      BETWEEN DATE '2007-01-01' 
                      AND     DATE '2008-12-31'
                      EACH INTERVAL '1' MONTH
       WITH DELETE;

Assume the previously defined CREATE TABLE definition for orders_cp. You could submit the following ALTER TABLE MODIFY request to alter the row partitioning expressions at both levels:

     ALTER TABLE orders_cp 
       MODIFY PRIMARY INDEX
       DROP RANGE BETWEEN     0 
                  AND        99
       ADD RANGE  BETWEEN 50000 
                  AND     50199 
                  EACH 100,
       DROP RANGE WHERE PARTITION#L2 = 1
       WITH DELETE;

The number of partitions at level 1 is increased by 2 and the number of partitions at level 2 decreases by 1. At the same time, the ADD value for level 2 increases from 0 to 1.