Aggregate join indexes offer an extremely efficient, cost-effective method of resolving requests that frequently specify the same aggregation operations on the same column or columns. When aggregate join indexes are available, the system does not have to repeat aggregation calculations for every request.
You can define an aggregate join index on two or more tables or on a single table. A single-table aggregate join index includes:
- A subset of the columns in a base table
- Additional columns for the aggregate summaries of the base-table columns
You can create an aggregate join index using the GROUP BY clause and the following built-in aggregate functions.
- SUM
- COUNT
- MAX
- MIN
The following restrictions apply to defining an aggregate join index.
- Only the COUNT, MAX, MIN, and SUM aggregate functions are valid in any combination.
COUNT DISTINCT and SUM DISTINCT are not valid.
- To avoid overflow, type the COUNT, MAX, MIN, and SUM columns in an aggregate join index definition as FLOAT. Vantage enforces this restriction as follows:
- If you do not define an explicit data type for a COUNT, MAX, MIN, or SUM column, Vantage uses the FLOAT data type.
- If you define a COUNT, MAX, MIN, or SUM column as anything other than FLOAT, Vantage returns an error and does not create the aggregate join index.
Multiple aggregate functions are based on the SUM, MAX, MIN, and COUNT functions, so for aggregate functions that you cannot specify in an aggregate join index, you can combine these four functions to create an aggregate join index to resolve requests that use more complicated aggregate functions.
A simple example is using the COUNT and SUM functions to compute an average.
