Aggregate Join Index With EXTRACT Function - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

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;