Example: Drop and Add Partition Ranges and Delete Rows Outside the Defined Ranges - 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™

The following ALTER TABLE request modifies the table created in Table Definitions for Examples. It is valid if there are 0 or more rows with o_orderdate values between January 1 1992 (DATE ‘1992-01-01’) and December 31 1992 (DATE ‘1992-12-31’). As a result of the WITH DELETE specification, those rows, if any, are deleted from orders because they do not belong to any partition in the new partitioning expression.

The specified primary index is the same as the existing primary index for the table because the request does not include changes to the primary index. The EACH clause after the DROP clause is ignored, but the EACH clause after the ADD clause is processed.

     ALTER TABLE orders 
     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
     WITH DELETE;

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
     WITH DELETE;