Optimal Reconciliation of Partitioning Expressions Based On Updatable Current Date and Timestamp - Analytics Database - Teradata Vantage

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’.