17.05 - Example: Modifying Character Partitioning for a Row-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 the following table:

     CREATE TABLE orders (
       o_orderkey INTEGER NOT NULL,
       o_custkey INTEGER,
       o_orderstatus CHAR(1) NOT CASESPECIFIC,
       o_totalprice DECIMAL(13,2) NOT NULL,
       o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHAR(21),
       o_comment VARCHAR(79))
     PRIMARY INDEX (o_orderkey)
     PARTITION BY (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 24,
                   RANGE_N(o_orderpriority BETWEEN 'high' 
                                           AND     'highest',
                                                   'low' 
                                           AND     'lowest',
                                                   'medium' 
                                           AND     'medium',
                   NO RANGE OR UNKNOWN))
     UNIQUE INDEX (o_orderkey);

The following ALTER TABLE request is valid only if orders is empty:

     ALTER TABLE orders 
     MODIFY 
     PARTITION BY (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 ),
                   CASE_N(o_orderstatus = 'S',
                          o_orderstatus = 'C',
                          o_orderstatus = 'F',
                          o_orderstatus = 'T', 
                   NO CASE OR UNKNOWN) );

Assuming the preceding table definition for orders , 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 row 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;
     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;
     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;

Assuming the table definition for orders , the following ALTER TABLE request is valid even if the table is populated with rows. However, the range being dropped must be empty because the request does not specify a WITH clause.

     ALTER TABLE orders 
     MODIFY PRIMARY INDEX
     DROP RANGE#L1 BETWEEN 0 
                   AND   999
     ADD RANGE BETWEEN 50000 
               AND     50999;

These ALTER TABLE MODIFY ADD/DROP RANGE requests only execute if the session collation matches the PPI collation and the session mode matches the session mode in effect when the character PPI was created.

The following example, which is based on the same original schema, returns an error:

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

The following ADD/DROP request involving the character row partitioning level of the orders 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.

     ALTER TABLE orders 
     MODIFY PRIMARY INDEX
     DROP RANGE#L3 WHERE PARTITION#L3 = 4
     ADD RANGE#L3 UNKNOWN;