Aggregate Join Indexes | Database Design | Teradata Vantage - Aggregate Join Indexes - Advanced SQL Engine - Teradata Database

An aggregate join index is a database object created using the CREATE JOIN INDEX statement, but specifying one or more columns that are derived from an aggregate expression. An aggregate join index is a join index that specifies MIN, MAX, SUM, COUNT, or that extracts a DATE value aggregate operations. No other aggregate functions are permitted in the definition of a join index; however, most of the other simple aggregate functions can be derived from these using column expressions. You can create aggregate join indexes as either single-table or as multitable join indexes. Aggregate join indexes can also be sparse (see Sparse Join Indexes).

Functions of Aggregate Join Indexes

The primary function of an aggregate join index is to provide the Optimizer with a high-performing, cost-effective means for satisfying any query that specifies a frequently made aggregation operation on one or more columns.

In other words, aggregate join indexes permit you to define a persistent summary table without violating the normalization of the database schema. This allows a join index to precompute an aggregate value that would otherwise potentially require a table scan and sort operation.

Aggregate join indexes can be especially helpful for queries that roll up values for dimensions other that the primary key dimension, which would otherwise require redistribution.

Related Strategies

Table Definitions

This example set uses the following table definitions.

     CREATE TABLE customer (
       c_custkey    INTEGER NOT NULL,
       c_name       CHARACTER(26) CASESPECIFIC NOT NULL,
       c_address    VARCHAR(41),
       c_nationkey  INTEGER,
       c_phone      CHARACTER(16),
       c_acctbal    DECIMAL(13,2),
       c_mktsegment CHARACTER(21),
       c_comment    VARCHAR(127))
     UNIQUE PRIMARY INDEX (c_custkey);

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'YYYY-MM-DD' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_clerk         CHARACTER(16),
       o_shippriority  INTEGER,
       o_comment       VARCHAR(79))
     UNIQUE PRIMARY INDEX (o_orderkey);

Query Plan: No Aggregate Join Index Defined

Without an aggregate join index, a typical execution plan for this query might involve the following stages:

1. Redistribute orders into spool.
2. Sort the spool on o_custkey.
3. Merge join the sorted spool and the customer file.
4. Aggregate the result of the merge join.

Aggregate Join Index Definition

Suppose you define the following aggregate join index, which aggregates o_totalprice over a join of orders and customer.

     CREATE JOIN INDEX ord_cust_idx AS
      SELECT c_nationkey, SUM(o_totalprice(FLOAT))
             AS  price, o_orderdate
      FROM orders, customer
      WHERE o_custkey = c_custkey
      GROUP BY c_nationkey, o_orderdate
      ORDER BY o_orderdate;

Query Plan for Aggregate Join Index

The execution plan produced by the Optimizer for this query includes an aggregate step on the aggregate join index, which is much smaller than either one of the join tables. You can confirm this by performing an EXPLAIN on the query.

    EXPLAIN SELECT COUNT(*), SUM(o_totalprice)
            FROM orders, customer
            WHERE o_custkey = c_custkey
            AND   o_orderdate > DATE ‘2005-09-20’
            AND   o_orderdate < DATE ‘2005-10-15’
            GROUP BY c_nationkey;

    *** Help information returned. 18 rows.
     *** Total elapsed time was 3 seconds.

   Explanation
   ---------------------------------------------------------------------
     1) First, we lock TPCD.ord_cust_idx for read on a
        reserved RowHash to prevent a global deadlock.
     2) Next, we do a SUM step to aggregate from join index table
        TPCD.ordcustidx by way of an all-rows scan with a condition of (
        "(TPCD.ord_cust_idx.O_ORDERDATE > DATE '2005-09-20') AND
        (TPCD.ord_cust_idx.O_ORDERDATE < DATE '2005-10-15')"), and the
        grouping identifier in field 1. Aggregate Intermediate Results
        are computed globally, then placed in Spool 2. The size of Spool
        2 is estimated to be 1 row.
     3) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
        an all-rows scan into Spool 1, which is built locally on the AMPs.
        The size of Spool 1 is estimated with no confidence to be 1 row.
        The estimated time for this step is 0.17 seconds.
     4) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.
   -> The contents of Spool 1 are sent back to the user as the result of
      statement 1.

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.