Aggregate Join Indexes - Teradata VantageCloud Lake

If your application supports repeated access to the same table using aggregation along the same set of dimensions, 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 can 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 is then processed as a single-AMP request.