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

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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:

j_sales      
store_ID amount sale_date PARTITION
1 2000.00 2009-01-01 1
1 3500.00 2009-01-15 1
1 2000.00 2008-12-15 2
1 5000.00 2009-04-01 1

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:

j_sales      
store_ID amount sale_date PARTITION
1 2000.00 2009-01-01 2
1 3500.00 2009-01-15 2
1 2000.00 2008-12-15 2
1 5000.00 2009-04-01 1