CREATE AGGREGATING INDEX

Different syntax is used when creating an aggregating index on an empty table or a table populated with data. After an aggregating index is created, Firebolt automatically updates the index as new data is ingested. For more information, see [Using aggregating indexes](../../using-indexes/using-aggregating-indexes.md.

Syntax–aggregating index on an empty table

CREATE AGGREGATING INDEX <agg_index_name> ON <fact_table_name> (
  <key_column>[,...<key_columnN>],
  <aggregation>[,...<key_columnN>]
);

The index is populated automatically as data is loaded into the table.

Syntax–aggregating index on a populated table

CREATE AGGREGATING INDEX [IF NOT EXISTS] <agg_index_name> ON <fact_table_name> (
  <key_column>[,...<key_columnN>],
  <aggregation>[,...<aggregationN>]
);

Parameters

Parameter	Description
`<index>`	Specifies a unique name for the index
`<table>`	Specifies the name of the fact table referenced by this index
`<column>`	Specifies column name from the `<table>` used for the index
`<aggregation>`	Specifies one or more aggregation functions to be applied on a `<key_column>`, such as `SUM`, `COUNT`, `AVG`, and more.

Example–create an aggregating index

In the following example, we create an aggregating index on the fact table my_fact, to be used in the following query:

SELECT
  product_name,
  count(DISTINCT source),
  sum(amount)
FROM
  my_fact
GROUP BY
  product_name;

The aggregating index is created with the statement below.

CREATE AGGREGATING INDEX my_fact_agg_idx ON my_fact (
  product_name,
  count(distinct source),
  sum(amount)
);

To benefit from the performance boost provided by the index, include in the index definition all columns and measurements that the query uses.