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.