CREATE AGGREGATING INDEX

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

Parameter	Description
`<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)
);

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Example

See also

​Syntax

​Parameters

​Example

​See also

Syntax

Parameters

Example

See also