ApsaraDB for SelectDB:Synchronous materialized view

Sync vs. async materialized views

Choose the right type before you start building.

Use a synchronous materialized view when your workload targets a single table and needs real-time consistency. Use an asynchronous materialized view for multi-table joins or richer aggregation logic.

Use cases

• Aggregation acceleration: Pre-compute GROUP BY aggregations that run repeatedly on large datasets.

• Prefix index matching: Create a view with a different leading sort column to match queries that cannot use the base table's prefix index.

• Pre-filtering: Store a filtered subset of the base table to reduce scan volume.

• Expression pre-computation: Materialize complex computed columns so queries read results directly.

When to create a materialized view

Create a synchronous materialized view when all of the following are true:

• The query targets a single table.

• The query runs frequently.

• The query is expensive (heavy aggregation, large scan, or complex expression).

Skip the materialized view when any of the following apply:

• The query is cheap and fast already.

• Different aggregate functions are needed on the same column (not supported).

• You already have more than 10 materialized views on the table — each additional view slows down every data import, because base table and all its views are updated together.

Limitations

• Direct queries are not supported. Write queries against the base table. SelectDB picks the best matching materialized view automatically.

• Unique model restriction: In the Unique model, a synchronous materialized view can only reorder columns — it cannot aggregate data.

• Import performance: Each materialized view adds overhead to every data import. More than 10 materialized views on a single table can significantly slow down imports.

Create a materialized view

CREATE MATERIALIZED VIEW <mv_name> AS <query>
[PROPERTIES ("key" = "value")]

SELECT select_expr [, select_expr ...]
FROM <base_table_name>
[GROUP BY column_name [, column_name ...]]
[ORDER BY column_name [, column_name ...]]

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;

CREATE MATERIALIZED VIEW k1_k2 AS
SELECT k2, k1 FROM duplicate_table;

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

CREATE MATERIALIZED VIEW k1_k2_sumk3 AS
SELECT k1, k2, sum(k3)
FROM duplicate_table
GROUP BY k1, k2;

Check creation status

Creating a materialized view is an asynchronous operation. After submitting the CREATE statement, SelectDB builds the view in the background. Track progress with:

SHOW ALTER TABLE MATERIALIZED VIEW FROM <database>;

Result fields:

The view is ready to use when State is FINISHED.

Example:

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 |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+

List materialized views

List all materialized views on a base table along with their schemas:

DESC <table_name> ALL;

Example:

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

Retrieve the SQL used to create a materialized view:

SHOW CREATE MATERIALIZED VIEW <mv_name> ON <table_name>;

This command only works for existing materialized views. Deleted views cannot be queried.

Example:

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 ALTER TABLE MATERIALIZED VIEW FROM <database>.<table_name>;

CANCEL ALTER TABLE MATERIALIZED VIEW FROM test_db.duplicate_table;

DROP MATERIALIZED VIEW [IF EXISTS] <mv_name> ON <table_name>;

-- List materialized views before dropping
DESC duplicate_table ALL;

-- Drop the view named k1_k2
DROP MATERIALIZED VIEW k1_k2 ON duplicate_table;

-- Confirm the view is gone
DESC duplicate_table ALL;

Query automatic matching

After a materialized view is created and its State is FINISHED, all existing queries continue to target the base table unchanged. SelectDB transparently selects the best matching materialized view and rewrites the query internally.

The matching table for aggregate functions is:

When bitmap or hll aggregation matches, SelectDB rewrites the query's aggregation operator based on the materialized view's schema.

To confirm a query is hitting a materialized view, run EXPLAIN against it:

EXPLAIN <your_query>;

In the output, look for OlapScanNode. The rollup attribute shows which index is being scanned. If it shows the materialized view name instead of the base table name, the match is confirmed. For more details on reading EXPLAIN output, see Query Explain.

Best practices

SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id))
FROM advertiser_uv
GROUP BY advertiser, channel;

EXPLAIN SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

CREATE MATERIALIZED VIEW approx_uv AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

Troubleshooting

Error: `DATA_QUALITY_ERR: "The data quality does not satisfy, please check your data."`

This error occurs when data quality issues or schema changes cause memory usage to exceed limits during view construction. If the cause is memory pressure, increase the memory_limitation_per_thread_for_schema_change_bytes parameter.

Two additional causes specific to bitmap views:

• Negative integers in source data: BITMAP_UNION only supports positive integers. If the column contains negative values, view creation fails. Check the source data and filter or transform negative values before creating the view.

• String columns: Use bitmap_hash or bitmap_hash64 to compute a hash value from string columns before applying bitmap_union.

What's next

• Query Explain — understand EXPLAIN output to debug query planning and verify materialized view matching.