17.00 - 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.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
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.