17.05 - Example: Creating a Join Index Using an Updatable CURRENT_DATE Function - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

You can define the partitioning expression for a join index that uses an updatable current date or current timestamp function. This enables the partition that contains the most recent data to be as narrow as possible, thus enabling more efficient access to the rows in that partition.

An additional benefit of updatable current dates and updatable current timestamps is that a partitioning expression based on them does not need to be redefined as time passes. Instead, you can just update the expressions and partitioning using an ALTER TABLE TO CURRENT request (see ALTER TABLE TO CURRENT) to reconcile the date or timestamp values to a newer date or timestamp, providing a simple way to define and maintain the partitioning expression for a join index and avoiding the need to drop and recreate an index whose current date or timestamp values are no longer current.

Suppose you create the following table on January 1, 2009.

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX (cust_no) 
     PARTITION BY CASE_N(policy_expiration_date>=CURRENT_DATE,
                         policy_expiration_date<CURRENT_DATE 
                     AND policy_expiration_date>=CURRENT_DATE-
                                                 INTERVAL '3' MONTH);

On January 1, 2009, you decide to create a join index on customer with a partitioning expression that specifies a updatable CURRENT_DATE function. The index is designed to contain only the data for the current quarter:

     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);

With a resolved CURRENT_DATE of January 1, 2009, join index j_sales contains the following rows.

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 for j_sales.

     ALTER TABLE j_sales TO CURRENT;

The rows in the join index are reconciled as follows.

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

See Example: Altering the Partitioning of a Join Index Using ALTER TABLE TO CURRENT for an example of how you can reconcile the partitioning for this join index using an ALTER TABLE TO CURRENT request.