17.10 - Optimal Reconciliation of Partitioning Expressions Based On Updatable Current Date and Timestamp - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

The following case applies to altering the partitioning expression of a populated table using an ALTER TABLE TO CURRENT statement.

Consider the following partitioning definition.

     CREATE TABLE ppi_1 (
       i INTEGER,
       j DATE)
     PRIMARY INDEX(i)
     PARTITION BY CASE_N(j <  DATE '2011-01-01',
                         j >= DATE '2011-01-01')

Assume that the current date is DATE '2011-01-01'. You cannot alter the partitioning definition for table ppi_1 using an ordinary ALTER TABLE statement after ppi_1 has been populated with rows, but you can alter its definition using an ALTER TABLE TO CURRENT statement if you define the table using a CURRENT_DATE function instead of specifying a simple date as the redefined table definition ppi_2 demonstrates.

This redefinition of the partitioning expression for ppi_1, which replaces the DATE specification in its partitioning expression with a CURRENT_DATE specification, can be modified using an ALTER TABLE TO CURRENT statement.

     CREATE TABLE ppi_2 (
       i INTEGER,
       j DATE)
     PRIMARY INDEX(i)
     PARTITION BY CASE_N(j <  CURRENT_DATE,
                         j >= CURRENT_DATE)

For this example, the value of CURRENT_DATE resolves to DATE ‘2011-01-01’.