Feature Support: Asynchronous Materialized Views
Asynchronous materialized views are supported from StarRocks v2.4 onwards. Asynchronous materialized views are designed to accelerate complex queries with joins or aggregations on large tables within StarRocks or in data lakes. The performance difference can be significant when a query is run frequently or is sufficiently complex. Additionally, asynchronous materialized views are especially useful for building mathematical models upon your data warehouse.
This document outlines the boundaries of competence for asynchronous materialized views and the supported version of the features involved.
DDL Features
| Feature | Description | Supported Version(s) |
|---|---|---|
| Auto Analyze | Automatically collects statistics after the materialized view is created to avoid rewrite failures. | v3.0+ |
| Random Bucketing | Enables random bucketing strategy for materialized views by default. | v3.1+ |
| Deferred Refresh | Supports specifying whether to refresh the materialized view immediately after being created by using DEFERRED or IMMEDIATE in CREATE MATERIALIZED VIEW. | v3.0+ |
| Order By | Supports specifying the sort key for materialized views using ORDER BY. | v3.1+ |
| Window/CTE/Union/Subquery | Supports using window functions, CTEs, Unions, and subqueries in materialized views. | v2.5+ |
| ALTER ACTIVE | Activates invalid materialized views after Schema Changes in base tables using the ACTIVE keyword in ALTER MATERIALIZED VIEW. | v2.5.7+ v3.0.1+ v3.1+ |
| REFRESH SYNC MODE | Supports synchronous execution for materialized view refresh tasks by using WITH SYNC MODE keywords in REFRESH MATERIALIZED VIEW. | v2.5.8+ v3.0.4+ v3.1+ |
| Intermediate Result Spilling | Supports enabling Intermediate Result Spilling using the enable_spill property to avoid OOM during materialized view construction. | v3.1+ |
| Resource Group | Supports specifying resource groups using the resource_group property for materialized view construction to achieve resource isolation. | v3.1+ |
| Materialized View on View | Supports creating materialized views based on logical views. | v3.1+ |
| Swap Materialized View | Supports atomically replacing materialized view using the SWAP WITH keywords in ALTER MATERIALIZED VIEW. | v3.1+ |
| CREATE INDEX ON Materialized View | Supports creating indexes on materialized views to accelerate point queries. | v3.0.7+ v3.1.4+ v3.2+ |
| AUTO ACTIVE | Automatically activates invalid materialized views in the background with exponential backoff, stopping after the interval reaches 60 minutes. | v3.1.4+ v3.2+ |
| Backup and Restore | Supports Backup and Restore for materialized views. | v3.2+ |
| Object Dependencies | Provides a system-defined view sys.object_dependencies to clarify the dependency relationship between materialized views and base tables. | v3.2+ |
Variables
| Variable | Description | Default | Supported Version(s) |
|---|---|---|---|
| enable_materialized_view_rewrite | Whether to enable materialized view query rewrite. | true | v2.5+ |
| enable_materialized_view_for_insert | Whether to enable materialized view query rewrite for INSERT statements. | false | v2.5.18+ v3.0.9+ v3.1.7+ v3.2.2+ |
| materialized_view_rewrite_mode | Mode of materialized view query rewrite. | DEFAULT | v3.2+ |
| optimizer_materialized_view_timelimit | Maximum time can be used for materialized view query rewrite, after which query rewrite is abandoned and the Optimizer process continues. | 1000 | v3.1.9+ v3.2.5+ |
| analyze_mv | Method of collecting statistics after the materialized view is refreshed. | SAMPLE | v3.0+ |
| enable_materialized_view_plan_cache | Whether to enable plan cache for materialized views. By default, 1000 materialized view plans are cached. | TRUE | v2.5.13+ v3.0.7+ v3.1.4+ v3.2.0+ v3.3.0+ |
| query_including_mv_names | Whitelist of the materialized views that can be used for query rewrite. | v3.1.11+ v3.2.5+ | |
| query_excluding_mv_names | Blacklist of the materialized views that can be used for query rewrite. | v3.1.11+ v3.2.5+ | |
| cbo_materialized_view_rewrite_related_mvs_limit | Maximum number of candidate materialized views in the Plan stage. | 64 | v3.1.9+ v3.2.5+ |
Properties
| Property | Description | Supported Version(s) |
|---|---|---|
session.<property_name> | Prefix of session variables used for materialized view construction, for example, session.query_timeout and session.query_mem_limit. | v3.0+ |
| auto_refresh_partitions_limit | Maximum number of materialized view partitions to be refreshed each time an automatic refresh is triggered. | v2.5+ |
| excluded_trigger_tables | Base tables whose updates will not trigger the materialized view automatic refresh. | v2.5+ |
| partition_refresh_number | Number of partitions to be refreshed in each batch when the refresh task is executed in batches. | v2.5+ |
| partition_ttl_number | The number of most recent materialized view partitions to retain. | v2.5+ |
| partition_ttl | The time-to-live (TTL) for materialized view partitions. This property is recommended over partition_ttl_number. | v3.1.4+ v3.2+ |
| force_external_table_query_rewrite | Whether to enable query rewrite for external catalog-based materialized views. | v2.5+ |
| query_rewrite_consistency | The query rewrite rule for materialized views built on internal tables. | v3.0.5+ v3.1+ |
| resource_group | The resource group to which the refresh tasks of the materialized view belong. | v3.1+ |
| colocate_with | The colocation group of the materialized view. | v3.1+ |
| foreign_key_constraints | The Foreign Key constraints when you create a materialized view for query rewrite in the View Delta Join scenario. | v2.5.4+ v3.0+ |
| unique_constraints | The Unique Key constraints when you create a materialized view for query rewrite in the View Delta Join scenario. | v2.5.4+ v3.0+ |
| mv_rewrite_staleness_second | Staleness tolerance for materialized view data during query rewrite. | v3.1+ |
| enable_query_rewrite | Whether the materialized view can be used for query rewrite. | v3.3+ |
Partitioning
| Alignment | Use Case | Supported Version(s) |
|---|---|---|
| Align partitions one-to-one (Date types) | Create a materialized view whose partitions correspond to the partitions of the base table one-to-one by using the same Partitioning Key. The Partitioning Key must be the DATE or DATETIME type. | v2.5+ |
| Align partitions one-to-one (STRING type) | Create a materialized view whose partitions correspond to the partitions of the base table one-to-one by using the same Partitioning Key. The Partitioning Key must be the STRING type. | v3.1.4+ v3.2+ |
| Align partitions with time granularity rollup (Date types) | Create a materialized view whose partitioning granularity is larger than that of the base table by using the date_trunc function on the Partitioning Key. The Partitioning Key must be the DATE or DATETIME type. | v2.5+ |
| Align partitions with time granularity rollup (STRING type) | Create a materialized view whose partitioning granularity is larger than that of the base table by using the date_trunc function on the Partitioning Key. The Partitioning Key must be the STRING type. | v3.1.4+ v3.2+ |
| Align partitions at a customized time granularity | Create a materialized view and customize the time granularity for its partitions by using the date_trunc function with the time_slice or date_slice function. | v3.2+ |
| Align partitions with multiple base tables | Create a materialized view whose partitions are aligned with those of multiple base tables, as long as the base tables use the same type of Partitioning Key. | v3.3+ |
Different Join Methods
- Single Fact Table (v2.4+): Establishing a partition mapping between the materialized view and the fact table ensures the automatic refresh of materialized view partitions when the fact table is updated.
- Multiple Fact Tables (v3.3+): Establishing a partition mapping between the materialized view and multiple fact tables that are joined/unioned at the same time granularity ensures the automatic refresh of materialized view partitions when any of the fact tables are updated.
- Temporal Dimension Table (v3.3+): Suppose the dimension table stores historical version data and is partitioned at a specific time granularity, and a fact table joins the dimension table at the same time granularity. Establishing a partition mapping between the materialized view and both the fact table and the dimension table ensures the automatic refresh of materialized view partitions when either table is updated.
Materialized views on external catalogs
| External Data Source | Supported Scenario and Version(s) | Stable Version(s) |
|---|---|---|
| Hive |
| v2.5.13+ v3.0.6+ v3.1.5+ v3.2+ |
| Iceberg |
| v3.1.5+ v3.2+ |
| Hudi |
| Not Stable |
| Paimon |
| Not Stable |
| DeltaLake |
| Not Stable |
| JDBC |
| Not Stable |
Query Rewrite
| Feature | Description | Supported Version(s) |
|---|---|---|
| Single Table Rewrite | Query rewrite with materialized views built on a single internal table. | v2.5+ |
| Inner Join Rewrite | Query rewrite for INNER/CROSS JOIN on internal tables. | v2.5+ |
| Aggregate Rewrite | Query rewrite for Joins with basic aggregations. | v2.5+ |
| UNION Rewrite | Predicate UNION compensation rewrite and partition UNION compensation rewrite on internal tables. | v2.5+ |
| Nested Materialized View Rewrite | Query rewrite with nested materialized views on internal tables. | v2.5+ |
| Count Distinct Rewrite (bitmap/hll) | Query rewrite for COUNT DISTINCT calculations into bitmap- or HLL-based calculations. | v2.5.6+ v3.0+ |
| View Delta Join Rewrite | Rewrite queries that join the tables that are the subset of the tables the materialized view joins. | v2.5.4+ v3.0+ |
| Join Derivability Rewrite | Query rewrite between different join types. | v2.5.8+ v3.0.4+ v3.1+ |
| Full Outer Join and Other Joins | Query rewrite for Full Outer Join, Semi Join, and Anti Join. | v3.1+ |
| Avg to Sum/Count Rewrite | Query rewrite for avg() to sum() / count() | v3.1+ |
| View-based Rewrite | Query rewrite with materialized views built upon views without transcribing queries against the view into queries against the base tables of the view. | v3.2.2+ |
| Count Distinct Rewrite (ArrayAgg) | Query rewrite for COUNT DISTINCT calculations into calculations with the array_agg_distinct function. | v3.2.5+ v3.3+ |
| Text-based Query Rewrite | Rewrite the query that has the identical abstract syntax tree with that of the materialized view's definition. | v3.3+ |
Diagnostic Features
| Feature | Usage Scenario | Supported Version(s) |
|---|---|---|
| TRACE REWRITE | Use the TRACE REWRITE statement to diagnose rewrite issues. | v2.5.10+ v3.0.5+ v3.1+ |
| Query Dump | Dump the information of a materialized view once it is queried. | v3.1+ |
| Refresh Audit Log | Record the SQL executed in the Audit Log when a materialized view is refreshed. | v2.5.8+ v3.0.3+ v3.1+ |
| Hit Audit Log | Record the hit materialized view and candidate materialized views in the Audit Log when a query is rewritten to a materialized view. | v3.1.4+ v3.2+ |
| Monitoring Metrics | Dedicated monitoring metrics for materialized views. | v3.1.4+ v3.2+ |