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.
An aggregate join index can be used to cover aggregate queries that only consider a subset of groups contained in the join index or have more join tables than the join index. In order to allow the aggregate join index to be used in this way, its definition must satisfy the following conditions:
In other words, you cannot specify an aggregated column as a partitioning column.
An aggregate join index can also be used to cover the following: