17.05 - Example: Modifying Character Partitioning for a Column-Partitioned Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

Assume you have created a column-partitioned version of the orders table from the previous example named orders_cp with column partitioning at level 1.

The following ALTER TABLE request is valid only if orders_cp is not populated with data.

     ALTER TABLE orders_cp
     MODIFY
     PARTITION BY (COLUMN,
                   RANGE_N(o_custkey   BETWEEN 0  
                                       AND 49999  
                                       EACH 1000),
                   RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' 
                                       AND     DATE '2006-12-31' 
                                       EACH INTERVAL '1' MONTH) ADD 48,
                   RANGE_N(o_orderstatus =  'S',
                           o_orderstatus =  'C',
                           o_orderstatus =  'F',
                           o_orderstatus =  'T', 
                   NO CASE OR UNKNOWN) );

Assuming the previous request succeeded because orders_cp was empty, you could submit any one of the following equivalent requests if there are no rows with an o_orderdate value between 2000-01-01 and 2000-12-31 to alter the partitioning expression for level 3.

     ALTER TABLE orders_cp 
     MODIFY
     DROP RANGE#L3 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;
     ALTER TABLE orders_cp 
     MODIFY
     DROP RANGE#L3 WHERE orders_cp.PARTITION#L2 BETWEEN 1 
                                                AND    12
     ADD RANGE#L3 BETWEEN DATE '2007-01-01' 
                  AND     DATE '2008-12-31'
                  EACH INTERVAL '1' MONTH;
     ALTER TABLE orders_cp
     MODIFY
     DROP RANGE#L3 WHERE PARTITION#L3 IN (1,2,3,4,5,6,7,8,9,10,11,12)
     ADD RANGE BETWEEN DATE '2007-01-01' 
               AND     DATE '2008-12-31' 
               EACH INTERVAL '1' MONTH;

Assuming that orders_cp is column-partitioned, the following ALTER TABLE … MODIFY request is valid even if the table is populated with data.

     ALTER TABLE orders_cp 
     MODIFY
     DROP RANGE#L2 BETWEEN 0 
                   AND   999
     ADD RANGE BETWEEN 50000 
               AND     54999
               EACH      100;

These ALTER TABLE MODIFY ADD/DROP RANGE requests only execute if the session collation and the session mode are the same as when the table was created.

The following example, which is based on the same original schema, returns an error if either the session collation or session mode are different than when orders_cp was created.

     ALTER TABLE orders_cp
     MODIFY
     DROP RANGE#L3 BETWEEN 'medium' 
                   AND     'medium'
     ADD RANGE BETWEEN     'medium' 
               AND         'urgent';

The following ADD/DROP request involving the character partitioning level of the orders_cp table is valid because it involves only the special row partitions NO RANGE OR UNKNOWN and UNKNOWN, with the NO RANGE OR UNKNOWN row partition being replaced with the UNKNOWN partition. Vantage deletes any rows that do not belong to any of the defined ranges for orders_cp

     ALTER TABLE orders_cp 
     MODIFY
     DROP RANGE#L4 WHERE PARTITION#L3 = 4
     ADD RANGE#L4 UNKNOWN
     WITH DELETE;