Skip to main content
Creates an aggregating index: a precomputed, automatically maintained rollup of a GROUP BY with aggregate functions. The optimizer substitutes it for the base-table scan when a query’s grouping structure matches. This page is the syntax reference. For how the index is maintained, the rule that a query must group by or filter on the leading grouping column, tuning, and limitations, see Aggregating index.

​
Syntax

CREATE AGGREGATING INDEX <index_name> ON <table_name> (
  <grouping_element> [, ...],
  <aggregation_element> [, ...]
);

​
Parameters

ParameterDescription
<index_name>A unique name for the aggregating index.
<table_name>The table on which the index is created.
<grouping_element>One or more columns or expressions used as grouping keys (dimensions). Order matters: a query must reference the leading key to use the index.
<aggregation_element>One or more aggregate functions (SUM, COUNT, AVG, …) applied to column expressions. A COUNT(*) is added automatically if not specified.

​
Example

CREATE AGGREGATING INDEX sales_agg_index ON sales (
  product_id,
  region,
  SUM(sales_amount),
  COUNT(DISTINCT order_id)
);

​
See also