Join index definitions support the EXTRACT function.
The following example illustrates the use of the EXTRACT function in the definition of an aggregate join index:
CREATE JOIN INDEX ord_cust_idx_2 AS SELECT c_nationkey, SUM(o_totalprice(FLOAT)) AS price, EXTRACT(YEAR FROM o_orderdate) AS o_year FROM orders, customer WHERE o_custkey = c_custkey GROUP BY c_nationkey, o_year ORDER BY o_year;
The aggregation is based only on the year of o_orderdate, which has fewer groups than the entire o_orderdate, so ord_cust_idx_2 is much smaller than ord_cust_idx.
On the other hand, the use for ord_cust_idx_2 is more limited than ord_custidx. In particular, ord_cust_idx_2 can only be used to satisfy queries that select full years of orders.
For example, ord_cust_idx_2 cannot be used for the query analyzed in Example: Creating and Using an Aggregate Join Index, but the following query does profit from its use:
SELECT COUNT(*), SUM(o_totalprice) FROM orders, customer WHERE o_custkey = c_custkey AND o_orderdate > DATE '1998-01-01' AND o_orderdate < DATE '1998-12-31' GROUP BY c_nationkey;