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.
That is, 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 and avoid 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, avoiding 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. To allow the aggregate join index to be used in this way, its definition must satisfy the following conditions:
- The grouping clause must include all columns that are specified in the grouping clause of the query.
- All columns in the query WHERE clause that join to tables not in the aggregate join index must be part of the join index definition.
- If you define row partitioning for an aggregate join index, its partitioning columns must be members of the column set specified in the GROUP BY clause of the index definition.
That is, you cannot specify an aggregated column as a partitioning column.
- An aggregate join index cannot be column partitioned.
An aggregate join index can also be used to cover the following:
- Requests that specify COUNT(DISTINCT) and extended grouping such as CUBE, ROLLUP, and GROUPING SETS.
- Requests that specify subqueries or spooled derived tables.
- The outer and the inner tables in a request that specifies an outer join.