CREATE AGGREGATING INDEX

Introduced or updated: v1.2.339

ENTERPRISE EDITION FEATURE

AGGREGATING INDEX is an Enterprise Edition feature. Contact Databend Support for a license.

Create a new aggregating index in Databend.

Syntax

CREATE [ OR REPLACE ] [ ASYNC ] AGGREGATING INDEX <index_name> AS SELECT ...

• ASYNC Option: Adding ASYNC is optional. It allows the index to be created asynchronously. This means the index isn't built right away. To build it later, use the REFRESH AGGREGATING INDEX command.

• When creating aggregating indexes, limit their usage to standard Aggregate Functions (e.g., AVG, SUM, MIN, MAX, COUNT and GROUP BY), while keeping in mind that GROUPING SETS, Window Functions, LIMIT, and ORDER BY are not accepted, or you will get an error: Currently create aggregating index just support simple query, like: SELECT ... FROM ... WHERE ... GROUP BY ....

• The query filter scope defined when creating aggregating indexes should either match or encompass the scope of your actual queries.

• To confirm if an aggregating index works for a query, use the EXPLAIN command to analyze the query.

Examples

This example creates an aggregating index named my_agg_index for the query "SELECT MIN(a), MAX(c) FROM agg":

-- Prepare data
CREATE TABLE agg(a int, b int, c int);
INSERT INTO agg VALUES (1,1,4), (1,2,1), (1,2,4), (2,2,5);

-- Create an aggregating index
CREATE AGGREGATING INDEX my_agg_index AS SELECT MIN(a), MAX(c) FROM agg;