Example: Dropping Ranges Without an EACH Clause - 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™

Assume the following table definition.

     CREATE TABLE t1 (
       i INTEGER, 
       d DATE)
     PRIMARY INDEX (i)
     PARTITION BY RANGE_N(d BETWEEN DATE '2000-01-01' 
                            AND     DATE '2000-12-31' 
                            EACH INTERVAL '1' MONTH);

The following ALTER TABLE request is valid. After the EACH clause is expanded, t1 has 12 existing ranges of one month each. The DROP RANGE operation is an attempt to drop a single four-month range that covers 4 of the existing one-month ranges. Vantage accepts this as a valid request to drop the 4 ranges within the range specified by the DROP clause.

     ALTER TABLE t1 
     MODIFY 
     DROP RANGE BETWEEN DATE '2000-01-01'
                AND     DATE '2000-04-30'
     WITH DELETE;

The new partitioning expression for t1 after this ALTER TABLE request completes is the following.

     RANGE_N(d BETWEEN DATE '2000-05-01' 
               AND     DATE '2000-12-31' 
               EACH INTERVAL '1' MONTH)

If table t1 were column-partitioned and named t1_cp , the preceding row-partitioned table example would have the same effect.