Create and manage materialized views based on intelligent recommendations - MaxCompute

How it works

The feature generates recommendations for public subqueries — subqueries that appear across multiple jobs — scored by impact. The score is based on three factors:

Number of beneficial jobs — how many jobs reuse the subquery
Amount of scanned data — how much data the subquery processes each run
Complexity — a score reflecting the computational weight of the subquery; used in the formula: computing resources = amount of scanned data × Complexity

Recommendations appear on the Materialized View Recommendations tab once all three conditions are met after you enable the feature:

Cycle tasks have run for more than three consecutive days
The input data for the public subquery exceeds 1,000,000 rows
The public subquery includes data reorganization operators, such as joins or aggregations

Limitations

Intelligent analysis is scoped to a single project. Cross-project analysis is not supported.
This feature is available in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), China (Chengdu), and Germany (Frankfurt).

Billing

Creating a materialized view generates two types of costs:

Storage costs — charged at the same rate as standard MaxCompute tables.
Computing costs — incurred when pay-as-you-go computing resources run the materialized view. Estimate these costs by multiplying the amount of scanned data by the Complexity value shown in the recommendation.

Before proceeding, review the Estimated Computing Resources Required for Creating All MVs metric on the Materialized View Recommendations tab to understand the one-time creation cost.

View recommendations

Prerequisites

Before viewing recommendations, make sure you have:

A MaxCompute project in a supported region
Cycle tasks that have run for more than three consecutive days after enabling the feature

View and filter recommendations

The Materialized View Recommendations tab shows projected impact metrics and a ranked list of public subqueries for which materialized views are recommended.

Log on to the MaxCompute console. In the top navigation bar, select a region.
In the left-side navigation pane, choose Intelligent Optimization > Intelligent Materialized Views.
On the Intelligent Materialized Views page, click the Materialized View Recommendations tab.

Set the following filters to scope your recommendations.

Parameter	Description
Project name	Select the MaxCompute project from the drop-down list.
Source table name	Enter one or more source table names. Fuzzy match is supported. Separate multiple names with commas (`,`).
Analyze time interval	Select a start and end date to scope the analysis to public subqueries from that period.

The tab displays two sections after filtering:

Impact summary — projected benefit across all recommendations:

Metric	Description
Estimated number of beneficial jobs per day	Estimated number of jobs per day that would benefit after creating the recommended materialized views.
Current CU-hour consumption and percentage per day of beneficial jobs	Ratio of compute unit (CU)-hours consumed by beneficial jobs to CU-hours consumed by all jobs per day. A higher percentage indicates greater impact.
Current computing resource consumption and percentage per day of beneficial jobs	Ratio of computing resources (amount of scanned data × Complexity) used by beneficial jobs to all jobs per day. A higher percentage indicates greater impact.
Estimated computing resources required for creating all MVs	One-time resource consumption for creating all recommended materialized views. Use this to estimate computing costs before proceeding.

Public subquery list — individual recommendations, ranked by impact:

Column	Description
MV summary	Summary of the public subquery: whether it includes an Aggregate operation, and the Inputs (source table names). Click the summary to open the Public Subquery Details panel.
Project	The project in which jobs using this subquery run.
Recommend rating	Recommendation strength, from one to five stars. More stars indicate a stronger recommendation.
Number of beneficial jobs	Number of jobs that use this public subquery.
Amount of scanned data	Data scanned by the public subquery.
Complexity	Complexity score of the public subquery.
Computing resources required for MV creation	Estimated one-time cost to create the materialized view: amount of scanned data × Complexity.
Analysis time	The time period analyzed for this subquery recommendation.
Operations included	Indicates whether the subquery contains Include Join Operation, Aggregate, or both. Use these values to filter the list.
Creation status	Whether a materialized view has been created for this subquery: Not Created or Created.
Actions	Create Materialized View (if not created) or View Materialized Views (if already created).

Inspect a public subquery

Click the value in the MV summary column to open the Public Subquery Details panel. This panel shows:

Public subquery no. — the backend-generated unique ID associated with the materialized view.
Public subquery — the SQL script of the subquery. Use the icons to expand the script to full screen, copy it, or download it.

Original SQL list (data in the last 5 days) — the original job instances that used this subquery in the last five days:

Column	Description
Instance ID	The job instance ID.
Submit project name	The project in which the job instance ran.
Submitted by	The user who submitted the job.
Execution time (s)	Time the subquery took to run, in seconds.
Original query SQL	Click View Details to inspect the full SQL, or Download to download the SQL script.

Create a materialized view

After reviewing a recommendation and its estimated cost, create a materialized view from the Materialized View Recommendations tab.

Important

Creating a materialized view generates storage and computing costs. See Billing for details before proceeding.

On the Materialized View Recommendations tab, find the recommendation and click Create Materialized View in the Actions column.

On the Create Materialized View page, configure the following parameters.

Parameter	Description
Materialized view name	Name for the materialized view. The default is `rec_mv_{$project_name}_{$timestamp}`. The name must start with a letter or digit and can contain letters, digits, and underscores (`_`).
Refresh type	How data in the materialized view is updated. Refresh at a Fixed Interval: refreshes at a set interval (minutes, hours, or days). Cron: refreshes on a Cron schedule.
Script	The SQL script used to create the materialized view. Use the icons to expand the script to full screen, copy it, or download it.

Click OK.

After creation, the materialized view appears on the MV Management and Benefits tab with a Creating status, which changes to Created when the view is ready.

Manage materialized views and track benefits

The MV Management and Benefits tab lists all materialized views in a project and shows their actual performance impact since creation.

In the left-side navigation pane, choose Intelligent Optimization > Intelligent Materialized Views.
On the Intelligent Materialized Views page, click the MV Management and Benefits tab.
Select a project and a profit statistics range (Since MV Creation or Previous 1 Day) to filter the data.

The tab shows two sections:

Aggregate benefit summary — totals across all materialized views in the selected project and time range:

Metric	Description
Hits	Total number of times all materialized views were called in the selected time range.
Saved computing duration	Difference between actual job duration (with materialized views) and estimated duration without them, within the selected range.
Saved CU-hours	Difference between actual CU-hours consumed (with materialized views) and estimated CU-hours without them.
Saved computing resources	Difference in computing resources (amount of scanned data × Complexity) used, compared to estimated usage without materialized views. Counts pay-as-you-go jobs only.
Savings in computing costs (catalog price)	Estimated cost savings: saved computing resources × CNY 0.3 per GB (pay-as-you-go Standard Edition SQL computing unit price).

Materialized view list — per-view details:

Column	Description
Materialized view name	Name of the materialized view.
Hits	Number of times this view was called in the selected profit statistics range.
Saved computing duration	Computing duration saved by this view within the selected range.
Saved CU-hours	CU-hours saved by this view within the selected range.
Saved computing resources	Computing resources saved (amount of scanned data × Complexity); pay-as-you-go jobs only.
Created at	When the materialized view was created.
Status	Created or Creating.
Actions	View Details to inspect the view's configuration and call history. Delete to remove the view.

View details of a materialized view

Click View Details in the Actions column to open the Materialized View Details panel. The panel contains three sections:

Basic information:

Field	Description
Created at	When the materialized view was created.
Table schema updated at	When the materialized view's table schema was last updated.
Storage size	Storage space occupied by the materialized view.
Refresh interval (minutes)	How frequently data in the view is refreshed.
Refreshed at	When the view's data was last refreshed.

Code details: The SQL script that defines the materialized view. Use the icons to expand to full screen, copy, or download the script.

Materialized view call details: Per-job breakdown of each job that called this materialized view:

Column	Description
Instance ID	Job instance ID. Click to view full job details.
Call MV execution time (s)	Time the job took to run after calling the materialized view, in seconds.
Save time (s)	Time saved for the job as a result of calling the materialized view.
Consumed CU-hours	CU-hours consumed by the job.
Saved CU-hours	CU-hours saved by the job after calling the materialized view.
Used computing resources	Computing resources used: amount of scanned data × Complexity.
Saved computing resources	Computing resources saved: amount of scanned data × Complexity.

Delete a materialized view

Click Delete in the Actions column on the MV Management and Benefits tab to remove a materialized view.