Example: Modifying Row Partitioning for Multilevel Column-Partitioned Tables - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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. 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. 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.