Automatic Materialized View (AutoMV)

Introduction

In a data warehouse, a materialized view is a key technique for optimizing repetitive computations. If your workload includes many queries that share frequent, time-consuming common subqueries (such as those involving JOINs or aggregations), you can use a materialized view to store the precomputed results. Subsequent queries can then read the results directly from the materialized view instead of recomputing them. This approach accelerates query performance and saves computing resources. For more information about materialized views, see Materialized view operations.

To simplify the analysis, creation, and maintenance of materialized views, MaxCompute introduces the Automatic Materialized View (AutoMV) feature. AutoMV simplifies the creation and management of materialized views and improves the user experience.

After you enable the Automatic Materialized View (AutoMV) feature in a MaxCompute project, the system automatically performs the following actions:

1. Continuous query monitoring: The system continuously monitors recent queries at the project level to identify common subqueries suitable for materialization.

2. Automatic creation of materialized views: The system periodically creates materialized views based on the identified candidates.

3. Intelligent query rewriting: The query optimizer automatically rewrites incoming queries to read data from the most efficient materialized views.

4. Adaptive storage resource management: This effectively manages the storage used by materialized views, preventing a significant increase in your project's total storage.

Limitations

• Supported regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), and China (Chengdu).

• An AutoMV can be created only if the source common subquery contains at least one of the following relational algebra operators: Project, Filter, JOIN, or Aggregate.

• AutoMV does not support cross-project usage. For example, if Project A and Project B could use the same materialized view, AutoMV creates a separate, identical view in each project because its scope is limited to a single project.

• To prevent performance degradation, the query rewriting process uses only materialized views that are exact matches for the original subquery.

Configure the AutoMV switch and storage limit

You can manage the AutoMV switch and set a storage limit for AutoMV using the MaxCompute console or SQL commands. After a limit is set, AutoMV stops writing data to existing materialized views if this limit is exceeded.

SETPROJECT odps.sql.auto.mv.quota.gb=1024; 

View AutoMV benefits and storage usage

The Automatic Materialized View (AutoMV) tab shows the benefits of the feature, including saved computing duration, saved CU-hours, reduced computing load, and cost savings. You can also monitor storage usage.

1. Log on to the MaxCompute console and select a region in the upper-left corner.

2. In the left-side navigation pane, choose Intelligent Optimization > Intelligent Materialized Views.

3. On the Intelligent Materialized Views page, on the AutoMV tab, select a MaxCompute project (or leave it blank for all projects) and a time range to view the project benefits, storage usage, and a list of specific AutoMVs.

   • Metrics:

     Metric	Description
     Hits	The number of times an AutoMV was used within the specified time range.
     Saved computing duration	The total reduction in computing time for jobs that used an AutoMV, compared to running them without one.
     Saved CU-hours	The reduction in CU-hour consumption for all jobs that used an AutoMV in the selected project, compared to running them without one.
     Reduced computing load	The reduction in computing load (scanned data amount × complexity) for all jobs that used an AutoMV in the selected project. This metric applies only to jobs that run on pay-as-you-go computing resources.
     Savings in computing costs (list price)	The estimated cost savings, calculated by multiplying the reduced computing load by the pay-as-you-go unit price. The price is CNY 0.3 per GB.
     Storage usage	The amount of storage that the AutoMV occupies.

   • AutoMV list parameters:

     Column	Description
     Materialized view name	The name of the Automatic Materialized View.
     Project	The project to which the AutoMV belongs.
     Hits	The number of times the AutoMV was used within the specified time range.
     Saved computing duration	The reduction in total computing duration for jobs that used this AutoMV, compared to their duration without it.
     Saved CU-hours	The reduction in CU-hour consumption for jobs that used this AutoMV, compared to their consumption without it.
     Reduced computing load	The reduction in computing load (scanned data amount × complexity) for jobs that used this AutoMV. This metric applies only to jobs that run on pay-as-you-go computing resources.
     Storage usage	The amount of storage that the AutoMV occupies.
     Actions	Click View Details to view the basic information and code details of the AutoMV. Basic information Creation time: The time when the AutoMV was created. Last structure update: The last time the AutoMV's table structure was updated. Storage size: The amount of storage that the AutoMV occupies. Code details: The SQL script that defines the AutoMV. You can view, copy, or download the SQL script.

Billing

After you enable AutoMV for a project, you are charged only for the automatically created materialized view storage at the standard storage price, with no additional service fees. For details about standard storage pricing, see Storage costs.

Related topics

• For more information about materialized view operations, see Materialized view operations.

• For more information about materialized view query rewriting, see Materialized view query rewriting.

• For more information about materialized view recommendation and management, see Materialized view recommendation and management.