Aggregate Join Index With EXTRACT Function
Join index definitions, both simple and aggregate, support the EXTRACT function. This example illustrates the use of the EXTRACT function in the definition of an aggregate join index.
Aggregate Join Index Definition
The index is defined as follows.
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.
Example Query Statement
While the join index defined for this example, ord_cust_idx_2, cannot be used for the query analyzed in “Query Plan for Aggregate Join Index” on page 506, the following query does profit from its use because dates are on year boundaries.
SELECT COUNT(*), SUM(o_totalprice)
FROM orders, customer
WHERE o_custkey = c_custkey
AND o_orderdate > DATE ‘2004-01-01’
AND o_orderdate < DATE ‘2004-12-31’
GROUP BY c_nationkey;