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.
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.
CREATE AGGREGATING INDEX [IF NOT EXISTS] <agg_index_name> ON <fact_table_name> ( <key_column>[,...<key_columnN>], <aggregation>[,...<aggregationN>] );
| ||Specifies a unique name for the index|
| ||Specifies the name of the fact table referenced by this index|
| ||Specifies column name from the |
| ||Specifies one or more aggregation functions to be applied on a |
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.