Link Search Menu Expand Document

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.