15.10 - Example: Using the EXTRACT Function With a Join Index Definition - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference

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;