Example: Dropping and Adding Partition Ranges - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

The following ALTER TABLE request modifies the tables orders and orders_cp created in Table Definitions for Examples. The outcome of the requests are identical to the table alterations performed in later ALTER TABLE requests in this example, but using different syntax.

The request is valid if there are no rows with values for o_orderdate between January 1, 1992 (DATE ‘1992-01-01’) and December 31, 1992 (DATE ‘1992-12-31’).

     ALTER TABLE orders 
     MODIFY
       DROP RANGE BETWEEN DATE '1992-01-01' 
                  AND     DATE '1992-12-31'
       ADD  RANGE BETWEEN  DATE '1999-01-01' 
                  AND      DATE '2000-12-31' 
                  EACH INTERVAL '1' MONTH;

The following request modifies the partitioning for the column-partitioned table orders_cp.

     ALTER TABLE orders_cp 
     MODIFY
       DROP RANGE BETWEEN DATE '1992-01-01' 
                  AND     DATE '1992-12-31'
       ADD  RANGE BETWEEN  DATE '1999-01-01' 
                  AND      DATE '2000-12-31' 
                  EACH INTERVAL '1' MONTH;

The following ALTER TABLE request modifies the table created in Table Definitions for Examples. The result is equivalent to the outcome of other requests in this example set using different syntax, and is valid if there are no rows with values for o_orderdate between January 1 1992 (DATE ‘1992-01-01’) and December 31 1992 (DATE ‘1992-12-31’).

     ALTER TABLE orders 
     MODIFY
       DROP RANGE WHERE PARTITION BETWEEN 1 
                                  AND    12
       ADD RANGE BETWEEN DATE '1999-01-01' 
                 AND     DATE '2000-12-31' 
                 EACH INTERVAL '1' MONTH;

The following request modifies the partitioning for the column-partitioned table orders_cp.

     ALTER TABLE orders_cp 
     MODIFY
       DROP RANGE BETWEEN DATE '1992-01-01' 
                  AND     DATE '1992-12-31'
       ADD  RANGE BETWEEN  DATE '1999-01-01' 
                  AND      DATE '2000-12-31' 
                  EACH INTERVAL '1' MONTH;

The following ALTER TABLE request modifies the table created in Table Definitions for Examples. The result is equivalent to the outcome of other requests in this example set and is valid if there are no rows with values for o_orderdate between January 1 1992 (DATE ‘1992-01-01’) and December 31 1992 (DATE ‘1992-12-31’).

     ALTER TABLE orders 
     MODIFY
       DROP RANGE WHERE Orders.PARTITION IN (1,2,3,4,5,6,7,8,9,10,11,12)
       ADD  RANGE BETWEEN DATE '1999-01-01' 
                  AND     DATE '2000-12-31' 
                  EACH INTERVAL '1' MONTH;

The following request modifies the partitioning for the column-partitioned table orders_cp.

     ALTER TABLE orders_cp 
     MODIFY
       DROP RANGE BETWEEN DATE '1992-01-01' 
                  AND     DATE '1992-12-31'
                  EACH INTERVAL '1' MONTH
       ADD  RANGE BETWEEN  DATE '1999-01-01' 
                  AND      DATE '2000-12-31' 
                  EACH INTERVAL '1' MONTH;