Repeated aggregation queries scanning the same rows on every request are a common performance bottleneck in analytical workloads. Synchronous materialized views let SelectDB pre-compute and store query results so it can serve them directly instead of recomputing on every request. When a query arrives, SelectDB automatically picks the best matching materialized view—your queries stay unchanged.
Before creating a materialized view, weigh the trade-off: each view consumes cluster storage and updates synchronously on every data import. A view matched by many queries delivers more value than one serving a single query.
How it works
A synchronous materialized view is a pre-computed dataset built from a SELECT statement and stored in a special table. SelectDB maintains it automatically:
Reduced query response time: Pre-stored computation results are read directly, skipping runtime aggregation.
No manual maintenance: SelectDB applies changes from the base table to the materialized view in the same transaction, using a built-in incremental update mechanism.
Strong consistency: Inserts, updates, and deletions on the base table are reflected in the materialized view immediately.
Unlike asynchronous materialized views, synchronous materialized views cannot be queried directly. Queries always target the base table; SelectDB rewrites and routes them to the matching materialized view transparently.
When to use materialized views
Synchronous materialized views are most effective when queries share common patterns:
Repeated aggregations: Your dashboard runs the same GROUP BY query thousands of times per day. Pre-computing the aggregation eliminates redundant work on each request.
Prefix index mismatches: A query filters on a column that is not the leading sort key. A materialized view can reorder the columns to match, enabling a prefix index hit.
Pre-filtering large tables: Certain queries always apply the same WHERE condition. The materialized view stores only the filtered rows, reducing the scan range.
Complex expression pre-computation: Expressions computed at query time—such as
bitmap_union-based exact deduplication—can be materialized to avoid repeated calculation.
Limitations
| Limitation | Details |
|---|---|
| No direct queries | Queries must target the base table. SelectDB routes them to the materialized view automatically. |
| Unique model aggregation | Materialized views on the Unique model can only reorder columns. Coarse-grained aggregation is not supported. |
| Import performance | Each data import updates all materialized views on a table synchronously. More than 10 materialized views on a single table can significantly slow down imports. |
Create a materialized view
Design principles
Before writing the CREATE statement, confirm the view pays off:
Abstract shared patterns: Extract the GROUP BY and aggregation logic that multiple queries have in common. A materialized view matched by many queries delivers more value than one serving a single query.
Cover common dimensions only: Not every dimension combination needs a materialized view. Focus on the ones that appear frequently in production queries to balance storage cost against query speed.
Syntax
CREATE MATERIALIZED VIEW <mv_name> AS <query>
[PROPERTIES ("key" = "value")]Parameters
| Parameter | Required | Description |
|---|---|---|
mv_name | Yes | Name of the materialized view. Must be unique among all materialized views on the same base table. |
query | Yes | SELECT statement that defines the materialized view. The query result becomes the stored data. |
properties | No | Optional configuration in ("key" = "value", ...) format. Supported keys: short_key (number of sort columns) and timeout (construction timeout in seconds). |
The query parameter follows this format:
SELECT select_expr [, select_expr ...]
FROM <base_view_name>
[GROUP BY column_name [, column_name ...]]
[ORDER BY column_name [, column_name ...]]| Parameter | Required | Description |
|---|---|---|
select_expr | Yes | Columns to include. Must include at least one single column. |
base_view_name | Yes | Base table name. Must be a single table, not a subquery. |
GROUP BY | No | Grouping columns. If omitted, no grouping is applied. |
ORDER BY | No | Sort columns. Must be declared in the same order as in select_expr. If omitted, sort columns are inferred automatically (see below). |
Automatic sort column inference (when ORDER BY is omitted):
Aggregation view: all GROUP BY columns become sort columns.
Non-aggregation view: the first 36 bytes of columns become sort columns.
If fewer than 3 columns are inferred, the first three columns are used.
If GROUP BY columns are present, sort columns must match them exactly.
SELECT statement constraints
Single-table only: JOIN is not supported.
Prohibited columns: auto-increment columns, constants, duplicate expressions, and window functions.
If the SELECT includes the table's partition key columns or bucketing columns, those columns must be Key columns in the materialized view.
Allowed clauses: WHERE, GROUP BY, ORDER BY.
Prohibited clauses: JOIN, HAVING, LIMIT, LATERAL VIEW.
Supported aggregate functions
Aggregate function parameters must be single columns.
sum(a)is valid;sum(a+b)is not.A column cannot appear in two different aggregate functions.
SELECT sum(a), min(a) FROM tableis not supported.
| Function | Supported formats |
|---|---|
| SUM, MIN, MAX, COUNT | Standard single-column form |
| BITMAP_UNION | BITMAP_UNION(TO_BITMAP(column)) — column must be an integer type, excluding largeint |
| BITMAP_UNION | BITMAP_UNION(column) — base table must be an Aggregate model |
| HLL_UNION | HLL_UNION(HLL_HASH(column)) — column type cannot be DECIMAL |
| HLL_UNION | HLL_UNION(column) — base table must be an Aggregate model |
Examples
Prepare the base table
CREATE TABLE duplicate_table (
k1 INT NULL,
k2 INT NULL,
k3 BIGINT NULL,
k4 BIGINT NULL
)
DUPLICATE KEY (k1, k2, k3, k4)
DISTRIBUTED BY HASH(k4) BUCKETS 3;DESC duplicate_table;
+-------+--------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+------+---------+-------+
| k1 | INT | Yes | true | N/A | |
| k2 | INT | Yes | true | N/A | |
| k3 | BIGINT | Yes | true | N/A | |
| k4 | BIGINT | Yes | true | N/A | |
+-------+--------+------+------+---------+-------+Example 1: Column subset without aggregation
CREATE MATERIALIZED VIEW k1_k2 AS
SELECT k2, k1 FROM duplicate_table;The resulting schema contains only k1 and k2, with no aggregation:
+-----------------+-------+--------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+-----------------+-------+--------+------+------+---------+-------+
| k2_k1 | k2 | INT | Yes | true | N/A | |
| | k1 | INT | Yes | true | N/A | |
+-----------------+-------+--------+------+------+---------+-------+Example 2: Custom sort order
CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;k2 becomes the leading sort column:
+-----------------+-------+--------+------+-------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+-----------------+-------+--------+------+-------+---------+-------+
| k2_order | k2 | INT | Yes | true | N/A | |
| | k1 | INT | Yes | false | N/A | NONE |
+-----------------+-------+--------+------+-------+---------+-------+Example 3: Aggregation
CREATE MATERIALIZED VIEW k1_k2_sumk3 AS
SELECT k1, k2, sum(k3) FROM duplicate_table GROUP BY k1, k2;Because no ORDER BY is declared on an aggregation view, SelectDB promotes the GROUP BY columns (k1, k2) as sort columns automatically:
+-----------------+-------+--------+------+-------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+-----------------+-------+--------+------+-------+---------+-------+
| k1_k2_sumk3 | k1 | INT | Yes | true | N/A | |
| | k2 | INT | Yes | true | N/A | |
| | k3 | BIGINT | Yes | false | N/A | SUM |
+-----------------+-------+--------+------+-------+---------+-------+Manage materialized views
Check creation status
Creating a materialized view is asynchronous. After submitting the request, SelectDB builds the view from historical data in the background.
SHOW ALTER TABLE MATERIALIZED VIEW FROM <database>;The result shows all materialized view creation tasks in that database:
SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db;
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| JobId | TableName | CreateTime | FinishTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt | 494350 | 133107 | FINISHED | | NULL | 2592000 |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+| Field | Description |
|---|---|
TableName | Source table of the materialized view data |
BaseIndexName | Base table name |
RollupIndexName | Name of the materialized view |
State | PENDING — scheduled; RUNNING — in progress; FINISHED — created successfully; CANCELLED — canceled |
Timeout | Construction timeout (default: 2,592,000 seconds) |
The materialized view is ready when State is FINISHED.
List materialized views on a table
DESC <table_name> ALL;Example: List all materialized views on duplicate_table:
DESC duplicate_table ALL;
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| duplicate_table | DUP_KEYS | k1 | INT | INT | Yes | true | NULL | | true | | |
| | | k2 | INT | INT | Yes | true | NULL | | true | | |
| | | k3 | BIGINT | BIGINT | Yes | true | NULL | | true | | |
| | | k4 | BIGINT | BIGINT | Yes | true | NULL | | true | | |
| | | | | | | | | | | | |
| k2_order | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mv_k1 | INT | INT | Yes | false | NULL | NONE | true | `k1` | |
| | | | | | | | | | | | |
| k1_k2 | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | |
| | | | | | | | | | | | |
| k1_k2_sumk3 | AGG_KEYS | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | |
| | | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mva_SUM__`k3` | BIGINT | BIGINT | Yes | false | NULL | SUM | true | `k3` | |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+View the creation statement
SHOW CREATE MATERIALIZED VIEW <mv_name> ON <table_name>;This command cannot retrieve the definition of a deleted materialized view.
Example:
-- Create the view
CREATE MATERIALIZED VIEW id_col1 AS SELECT id, col1 FROM table3;
-- View its definition
SHOW CREATE MATERIALIZED VIEW id_col1 ON table3;
+-----------+----------+----------------------------------------------------------------+
| TableName | ViewName | CreateStmt |
+-----------+----------+----------------------------------------------------------------+
| table3 | id_col1 | create materialized view id_col1 as select id,col1 from table3 |
+-----------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)Delete a materialized view
Cancel an in-progress creation
If the view is still being built, cancel it with:
CANCEL ALTER TABLE MATERIALIZED VIEW FROM <database>.<table_name>;| Parameter | Required | Description |
|---|---|---|
database | Yes | Database containing the base table |
table_name | Yes | Base table name |
Example: Cancel all in-progress materialized view builds on duplicate_table:
CANCEL ALTER TABLE MATERIALIZED VIEW FROM test_db.duplicate_table;Once the view is fully built (State = FINISHED), this command cannot cancel it. Use DROP MATERIALIZED VIEW instead.
Drop a completed materialized view
DROP MATERIALIZED VIEW [IF EXISTS] <mv_name> ON <table_name>;| Parameter | Required | Description |
|---|---|---|
IF EXISTS | No | Suppresses the error if the materialized view does not exist |
mv_name | Yes | Name of the materialized view to drop |
table_name | Yes | Base table name |
Example:
-- Confirm the view exists
DESC duplicate_table ALL;
-- Drop the view
DROP MATERIALIZED VIEW k1_k2 ON duplicate_table;
-- Confirm it is removed
DESC duplicate_table ALL;Query automatic matching
No query changes are needed after creating a materialized view. SelectDB automatically selects the most optimal materialized view, rewrites the query internally, and returns the result.
The matching rules between materialized view aggregations and query aggregations are:
| Query aggregation | Matched by materialized view aggregation |
|---|---|
| SUM | SUM |
| MIN | MIN |
| MAX | MAX |
| COUNT | COUNT |
| BITMAP_UNION, BITMAP_UNION_COUNT, COUNT(DISTINCT) | BITMAP_UNION |
| HLL_RAW_AGG, HLL_UNION_AGG, NDV, APPROX_COUNT_DISTINCT | HLL_UNION |
When a bitmap or hll aggregate function matches a materialized view, SelectDB rewrites the aggregation operator based on the materialized view's schema.
To confirm a query is hitting a materialized view, run EXPLAIN and check the rollup attribute in the OlapScanNode section. If the rollup value is the name of a materialized view (not the base table), the match succeeded. For more information, see Query Explain.
End-to-end example: ad UV with bitmap deduplication
This example walks through the full workflow: create a materialized view, wait for it to be built, and verify that queries hit it automatically.
Background
An ad analytics system stores click-level data in SelectDB. The following query computes unique visitors (UV) per ad and channel:
SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;COUNT(DISTINCT ...) rescans the raw data on every request. A materialized view with BITMAP_UNION pre-computes the deduplication so subsequent queries read from the smaller, pre-aggregated table. In SelectDB, COUNT(DISTINCT user_id) and BITMAP_UNION_COUNT(TO_BITMAP(user_id)) produce identical results.
Step 1: Set up the base table
CREATE TABLE advertiser_view_record (
time DATE,
advertiser VARCHAR(10),
channel VARCHAR(10),
user_id INT
)
DISTRIBUTED BY HASH(time);DESC advertiser_view_record ALL;
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
| advertiser_view_record | DUP_KEYS | time | DATE | DATEV2 | Yes | true | NULL | | true | | |
| | | advertiser | VARCHAR(10) | VARCHAR(10) | Yes | true | NULL | | true | | |
| | | channel | VARCHAR(10) | VARCHAR(10) | Yes | false | NULL | NONE | true | | |
| | | user_id | INT | INT | Yes | false | NULL | NONE | true | | |
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
4 rows in set (0.02 sec)Step 2: Create the materialized view
CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
Query OK, 0 rows affected (0.012 sec)user_idis an INT column. UseTO_BITMAPto convert it to bitmap type before applyingBITMAP_UNION. For String columns, usebitmap_hashorbitmap_hash64to compute a hash value first.
After creation, the table's schema shows the new materialized view:
DESC advertiser_view_record ALL;
+------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+
| advertiser_view_record | DUP_KEYS | time | DATE | DATEV2 | Yes | true | NULL | | true | | |
| | | advertiser | VARCHAR(10) | VARCHAR(10) | Yes | true | NULL | | true | | |
| | | channel | VARCHAR(10) | VARCHAR(10) | Yes | false | NULL | NONE | true | | |
| | | user_id | INT | INT | Yes | false | NULL | NONE | true | | |
| | | | | | | | | | | | |
| advertiser_uv | AGG_KEYS | mv_advertiser | VARCHAR(*) | VARCHAR(*) | Yes | true | NULL | | true | `advertiser` | |
| | | mv_channel | VARCHAR(*) | VARCHAR(*) | Yes | true | NULL | | true | `channel` | |
| | | mva_BITMAP_UNION__to_bitmap_with_check(CAST(`user_id` AS BIGINT)) | BITMAP | BITMAP | No | false | NULL | BITMAP_UNION | true | to_bitmap_with_check(CAST(`user_id` AS BIGINT)) | |
+------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+
8 rows in set (0.03 sec)Step 3: Wait for the build to finish
Run the following command and wait until State is FINISHED:
SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db;Step 4: Run the original query
No changes to the query are needed:
SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;SelectDB internally rewrites this to:
SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id))
FROM advertiser_uv
GROUP BY advertiser, channel;Step 5: Verify the match with EXPLAIN
Run EXPLAIN on the original query and look at the OlapScanNode section. Two signals confirm a successful match:
`rollup` = `advertiser_uv`: The
rollupvalue is the materialized view name, not the base table name. This means SelectDB is reading from the materialized view.`count(distinct)` rewritten as `bitmap_union_count(to_bitmap)`: SelectDB uses the bitmap-based path for exact deduplication.
EXPLAIN SELECT advertiser, channel, count(distinct user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| advertiser[#13] |
| channel[#14] |
| count(DISTINCT user_id)[#15] |
| PARTITION: UNPARTITIONED |
| |
| VRESULT SINK |
| |
| 4:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize) |
| | output: bitmap_union_count(partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#9])[#12] |
| | group by: mv_advertiser[#7], mv_channel[#8] |
| | cardinality=1 |
| | projections: mv_advertiser[#10], mv_channel[#11], bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#12] |
| | project output tuple id: 4 |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: HASH_PARTITIONED: time[#3] |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
| |
| 1:VAGGREGATE (update serialize) |
| | STREAMING |
| | output: partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT))[#2])[#9] |
| | group by: mv_advertiser[#0], mv_channel[#1] |
| | cardinality=1 |
| | |
| 0:VOlapScanNode |
| TABLE: default_cluster:test.advertiser_view_record(advertiser_uv), PREAGGREGATION: ON |
| partitions=1/1, tablets=10/10, tabletList=13531,13533,13535 ... |
| cardinality=1, avgRowSize=2745.0, numNodes=1 |
| pushAggOp=NONE |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
49 rows in set (0.11 sec)FAQ
Q: Creation fails with `DATA_QUALITY_ERR: "The data quality does not satisfy, please check your data."`
This error usually means the data contains values incompatible with the materialized view definition. Two common causes:
Negative integers in a bitmap column: Bitmap type only supports positive integers. If the source column contains negative values, materialized view creation will fail. Increase
memory_limitation_per_thread_for_schema_change_bytesor clean up the negative values in the source data before retrying.Memory limit exceeded during schema change: Increase the
memory_limitation_per_thread_for_schema_change_bytesparameter to give the build job more memory.
For String columns used with BITMAP_UNION, use bitmap_hash or bitmap_hash64 to compute a hash value before conversion.