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

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;