15.00 - Aggregate Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Aggregate Join Indexes

If your application supports repeated access to the same table using aggregation along the same set of dimensions, you should consider using aggregate join indexes to enhance the performance of those queries.

If you include one or more aggregating columns in the join index select list, then that index is an aggregate join index. Aggregate join indexes are dynamic summary tables, not snapshots. For example, the following aggregate join index computes a running sum on o_totalprice from the orders table:

     CREATE JOIN INDEX ordersum AS
       SELECT o_clerk, SUM(o_totalprice) AS sumprice
       FROM orders
       GROUP BY o_clerk
     PRIMARY INDEX(o_clerk);

Each time the o_totalprice column of orders is updated, a new sum is computed and stored in the ordersum aggregate join index. If there are frequent queries throughout the day that request summaries of the total prices for orders placed by a specific clerk, this join index would be able to deliver response times suitable for tactical queries like the following example:

     SELECT o_clerk, SUM(o_totalprice)
     FROM orders
     GROUP BY o_clerk
     WHERE o_clerk = 'Clerk#000046240';

Use the GROUP BY column set as the primary index of the join index if this is the column whose value is specified by the application. Each query that specifies a value for that column will then be processed as a single-AMP request.