Aggregate Join Index with EXTRACT Function - Teradata VantageCloud Lake

Join index definitions, simple and aggregate, support the EXTRACT function. This example shows the use of the EXTRACT function in the definition of an aggregate join index.

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.

The use for ord_cust_idx_2 is more limited than ord_custidx; ord_cust_idx_2 can only be used to satisfy queries that select full years of orders.

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