15.10 - join_index_name - 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

Name of the join index whose row partitioning is to be reconciled to a new current date or timestamp value.

Example: Altering the Row Partitioning of a Join Index

Assume that for the table in “Example: Altering the Row Partitioning of a Table When the Partitioning Expression Is Based on Multiple CURRENT_DATE Functions," you define a row-partitioned sparse join index j_sales on January 1, 2009 to contain the data of the current quarter in one row partition and all other data in another row partition for sale_amt greater than 2000.00.

     CREATE JOIN INDEX j_sales AS 
       SELECT * 
       FROM sales 
       WHERE sale_amt>=2000.00
     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 2008-04-01 1

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

     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 2008-04-01 2