17.10 - Example: Modifying Row Partitioning for Multilevel Column-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 create orders_cp, a column-partitioned version of the orders table that has column partitioning at level 1.

If orders_cp is not populated with data, you could submit the following ALTER TABLE MODIFY request to modify it to have only one level of row partitioning.

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

Table orders_cp has 2-byte partitioning.

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

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

or

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

or

     ALTER TABLE orders_cp 
       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 '2011-12-31' 
                 EACH INTERVAL '1' MONTH;

If there are 0, 1, or more rows with o_orderdate between 2000-01-01 and 2000-12-31 in orders_cp, you could submit the following ALTER TABLE request to alter the row partitioning level of orders_cp. In this case, Vantage deletes the rows in the dropped ranges because of the WITH DELETE specification.

This ALTER TABLE MODIFY request is valid for a populated table because adding more partitions to level 2 does not exceed the maximum defined for level 2. This is because all the excess combined partitions were assigned to level 2 as its default ADD option.

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

Assume you create 1 table using the previously defined orders_cp and another table called old_orders.

     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 request is one way to alter the row partitioning if there are 0, 1, 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 _cp.

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

Assume the previously defined CREATE TABLE definition for orders (see Example: Modifying Partitioning for Multilevel Partitioned Tables) with column partitioning added at level one and the primary index has been removed. The new table is named orders_cp. Table orders_cp has 8-byte partitioning. You could submit the following ALTER TABLE request to alter the partitioning expressions at both levels:

     ALTER TABLE orders_cp 
       MODIFY
       DROP RANGE BETWEEN     0 
                  AND       399
       ADD RANGE  BETWEEN 50000 
                  AND     50899 
                  EACH 100,
       DROP RANGE WHERE PARTITION#L3 = 1
       ADD RANGE  BETWEEN DATE '2007-01-01' 
                  AND     DATE '2007-01-31'
       WITH DELETE;

The number of partitions at level 2 is increased by 5 and the number of partitions at level 3 must remain the same because the default for level 3 is ADD 0, and the excess combined combinations are assigned to level 2, not leaving enough for level 3 to be able to increase its number of partitions.