Skip to main content
Version: Latest-3.3

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

FeatureDescriptionSupported Version(s)
Auto AnalyzeAutomatically collects statistics after the materialized view is created to avoid rewrite failures.v3.0+
Random BucketingEnables random bucketing strategy for materialized views by default.v3.1+
Deferred RefreshSupports specifying whether to refresh the materialized view immediately after being created by using DEFERRED or IMMEDIATE in CREATE MATERIALIZED VIEW.v3.0+
Order BySupports specifying the sort key for materialized views using ORDER BY.v3.1+
Window/CTE/Union/SubquerySupports using window functions, CTEs, Unions, and subqueries in materialized views.v2.5+
ALTER ACTIVEActivates 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 MODESupports 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 SpillingSupports enabling Intermediate Result Spilling using the enable_spill property to avoid OOM during materialized view construction.v3.1+
Resource GroupSupports specifying resource groups using the resource_group property for materialized view construction to achieve resource isolation.v3.1+
Materialized View on ViewSupports creating materialized views based on logical views.v3.1+
Swap Materialized ViewSupports atomically replacing materialized view using the SWAP WITH keywords in ALTER MATERIALIZED VIEW.v3.1+
CREATE INDEX ON Materialized ViewSupports creating indexes on materialized views to accelerate point queries.v3.0.7+
v3.1.4+
v3.2+
AUTO ACTIVEAutomatically activates invalid materialized views in the background with exponential backoff, stopping after the interval reaches 60 minutes.v3.1.4+
v3.2+
Backup and RestoreSupports Backup and Restore for materialized views.v3.2+
Object DependenciesProvides a system-defined view sys.object_dependencies to clarify the dependency relationship between materialized views and base tables.v3.2+

Variables

VariableDescriptionDefaultSupported Version(s)
enable_materialized_view_rewriteWhether to enable materialized view query rewrite.truev2.5+
enable_materialized_view_for_insertWhether to enable materialized view query rewrite for INSERT statements.falsev2.5.18+
v3.0.9+
v3.1.7+
v3.2.2+
materialized_view_rewrite_modeMode of materialized view query rewrite.DEFAULTv3.2+
optimizer_materialized_view_timelimitMaximum time can be used for materialized view query rewrite, after which query rewrite is abandoned and the Optimizer process continues.1000v3.1.9+
v3.2.5+
analyze_mvMethod of collecting statistics after the materialized view is refreshed.SAMPLEv3.0+
enable_materialized_view_plan_cacheWhether to enable plan cache for materialized views. By default, 1000 materialized view plans are cached.TRUEv2.5.13+
v3.0.7+
v3.1.4+
v3.2.0+
v3.3.0+
query_including_mv_namesWhitelist of the materialized views that can be used for query rewrite.v3.1.11+
v3.2.5+
query_excluding_mv_namesBlacklist of the materialized views that can be used for query rewrite.v3.1.11+
v3.2.5+
cbo_materialized_view_rewrite_related_mvs_limitMaximum number of candidate materialized views in the Plan stage.64v3.1.9+
v3.2.5+

Properties

PropertyDescriptionSupported 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_limitMaximum number of materialized view partitions to be refreshed each time an automatic refresh is triggered.v2.5+
excluded_trigger_tablesBase tables whose updates will not trigger the materialized view automatic refresh.v2.5+
partition_refresh_numberNumber of partitions to be refreshed in each batch when the refresh task is executed in batches.v2.5+
partition_ttl_numberThe number of most recent materialized view partitions to retain.v2.5+
partition_ttlThe 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_rewriteWhether to enable query rewrite for external catalog-based materialized views.v2.5+
query_rewrite_consistencyThe query rewrite rule for materialized views built on internal tables.v3.0.5+
v3.1+
resource_groupThe resource group to which the refresh tasks of the materialized view belong.v3.1+
colocate_withThe colocation group of the materialized view.v3.1+
foreign_key_constraintsThe Foreign Key constraints when you create a materialized view for query rewrite in the View Delta Join scenario.v2.5.4+
v3.0+
unique_constraintsThe 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_secondStaleness tolerance for materialized view data during query rewrite.v3.1+
enable_query_rewriteWhether the materialized view can be used for query rewrite.v3.3+

Partitioning

AlignmentUse CaseSupported 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 granularityCreate 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 tablesCreate 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 SourceSupported Scenario and Version(s)Stable Version(s)
Hive
  • Non-partitioned table: v2.5.4 & v3.0+
  • DATE and DATETIME-type partition: v2.5.4 & v3.0+
  • Transforming STRING-type Partition Key to DATE-type: v3.1.4 & v3.2+
  • Materialized views on Hive View: To be supported
  • Multi-level partitioning: To be supported
v2.5.13+
v3.0.6+
v3.1.5+
v3.2+
Iceberg
  • Non-partitioned table: v3.0+
  • DATE and DATETIME-type partition: v3.1.4 & v3.2+
  • Transforming STRING-type Partition Key to DATE-type: v3.1.4 & v3.2+
  • Materialized views on Iceberg View: To be supported
  • Partition Transform: v3.2.3
  • Partition-level refresh: v3.1.7 & v3.2.3
  • Multi-level partitioning: To be supported
v3.1.5+
v3.2+
Hudi
  • Non-partitioned table: v3.2+
  • DATE and DATETIME-type partition: v3.2+
  • Multi-level partitioning: To be supported
Not Stable
Paimon
  • Non-partitioned table: v2.5.4 & v3.0+
  • DATE and DATETIME-type partition: To be supported
  • Multi-level partitioning: To be supported
Not Stable
DeltaLake
  • Non-partitioned table: v3.2+
  • Partitioned table: To be supported
  • Multi-level partitioning: To be supported
Not Stable
JDBC
  • Non-partitioned table: v3.0+
  • Partitioned table: MySQL RangeColumn Partition v3.1.4
Not Stable

Query Rewrite

FeatureDescriptionSupported Version(s)
Single Table RewriteQuery rewrite with materialized views built on a single internal table.v2.5+
Inner Join RewriteQuery rewrite for INNER/CROSS JOIN on internal tables.v2.5+
Aggregate RewriteQuery rewrite for Joins with basic aggregations.v2.5+
UNION RewritePredicate UNION compensation rewrite and partition UNION compensation rewrite on internal tables.v2.5+
Nested Materialized View RewriteQuery 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 RewriteRewrite queries that join the tables that are the subset of the tables the materialized view joins.v2.5.4+
v3.0+
Join Derivability RewriteQuery rewrite between different join types.v2.5.8+
v3.0.4+
v3.1+
Full Outer Join and Other JoinsQuery rewrite for Full Outer Join, Semi Join, and Anti Join.v3.1+
Avg to Sum/Count RewriteQuery rewrite for avg() to sum() / count()v3.1+
View-based RewriteQuery 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 RewriteRewrite the query that has the identical abstract syntax tree with that of the materialized view's definition.v3.3+

Diagnostic Features

FeatureUsage ScenarioSupported Version(s)
TRACE REWRITEUse the TRACE REWRITE statement to diagnose rewrite issues.v2.5.10+
v3.0.5+
v3.1+
Query DumpDump the information of a materialized view once it is queried.v3.1+
Refresh Audit LogRecord the SQL executed in the Audit Log when a materialized view is refreshed.v2.5.8+
v3.0.3+
v3.1+
Hit Audit LogRecord 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 MetricsDedicated monitoring metrics for materialized views.v3.1.4+
v3.2+