Example: Altering the Partitioning of a Join Index Using ALTER TABLE TO CURRENT - Advanced SQL Engine - Teradata Database

Assume that you have created the following sales table on January 1, 2009.

     CREATE SET TABLE sales, NO FALLBACK (
       storeID   INTEGER,
       amount    DECIMAL(10,2),
       sale_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX (storeID) 
     PARTITION BY CASE_N(sale_date>=CURRENT_DATE/*latest quarter data*/,
                         sale_date<CURRENT_DATE 
                     AND sale_date>=CURRENT_DATE-INTERVAL '3' MONTH,
                         NO CASE);

You then define a PPI sparse join index j_sales on January 1, 2009 to contain only the data of the current quarter.

     CREATE JOIN INDEX j_sales AS 
       SELECT * 
       FROM sales 
       WHERE sale_date > CURRENT_DATE - INTERVAL '3' DAY
     PRIMARY INDEX (store_ID)
     PARTITION BY CASE_N(sale_date >= CURRENT_DATE, NO CASE);

Join index j_sales contains the following rows assuming that the resolved CURRENT_DATE is January 1, 2009:

On April 1, 2009, you submit the following ALTER TABLE TO CURRENT request on j_sales.

     ALTER TABLE j_sales TO CURRENT;

The rows in join index j_sales are reconciled as follows: