Overview

Basic Concepts of Materialized Views

The following table describes the basic concepts of materialized views.

Differences Among Materialized Views, Common Views, Common Tables

• A common view is a virtual table that stores only the SQL definition of the view, not the data of query results. During query rewriting, the query on the view is converted into a query on the base table.
• A materialized view is an entity table that stores both the SQL definition of the view and the data of query results. When data in any base tables changes, the materialized view becomes invalid. When the materialized view is refreshed, the data is recalculated to the latest data.

During query rewriting, the latest pre-calculated data is directly read from the materialized view based on the matching between the user's SQL statement and the SQL statement of the materialized view.

Basic Principles of Materialized Views

1. Data precomputation: Based on specified basic query statements (which can include operations such as join, aggregate, and filter), the query results are computed and stored as physical tables in one go when the materialized view is created or refreshed. This decouples the data from the base table.

2. Data consistency: When the data in the base table changes (INSERT, UPDATE and DELETE), the pre-stored data in the materialized view becomes outdated. The refresh mechanism is used to synchronize the changes in the base table, balancing query performance and data timeliness.

3. Transparent acceleration: When a user's SQL statement matches the SQL statement of a materialized view, query rewriting automatically selects the materialized view to read data. Service SQL statements do not need to be modified. The response time of complex queries and the system load can be reduced.

That is, materialized views provide millisecond-level response to complex queries at the cost of additional storage overhead and slight refresh performance loss.

Asynchronous and Synchronous Materialized Views

Advantages of Materialized Views

• Declarative programming

  One SQL statement is used to transfer data. You do not need to compile a complex stored procedure to transfer data between two tables. Indexes, locks, and data consistency are considered, minimizing the impact on services.

• Flexible refresh

  Full refresh and incremental refresh are supported.

  Manual refresh, scheduled refresh, and real-time refresh are supported. Cascading refresh by nested links is also supported.

• Stream processing

  The following layers of a data warehouse are created as materialized views: Data Warehouse Detail (DWD) layer for cleaning, deduplicating, and standardizing raw data, Data Warehouse Base (DWB) layer for integrating and associating wide tables, Data Warehouse House Service (DWS) layer for summarizing common aggregation indicators, and Application Data Store (ADS) for applying to data, such as specific reports. Incremental computing enables real-time, dynamic processing of data streams.

• Transparent acceleration

  Materialized views pre-calculate and store query results. When a query is initiated, materialized views can be queried and directly return results, improving query performance. Services do not need to be modified.

• Higher efficiency and lower costs

  Executing complex queries multiple times requires significant CPU and memory resources. Materialized views use transparent acceleration to rewrite complex and repeated computing into simple SQL statements for querying materialized views, significantly reducing cluster load.

• Instant materialization

  Materialized views are partitioned. The old partitions can be automatically eliminated, reducing the data management workload.

• Hot and cold partitions

  Materialized views are partitioned. The old partitions can be automatically downgraded to cold storage, thereby effectively reducing the overall storage costs.

Materialized View Syntax

Prerequisites

Materialized views of DWS is supported only by clusters of version 8.2.1.220 or later. To use materialized views, ensure that enable_matview is set to on.

• Create a materialized view.

  1 2 3 4 5 6 7 8 9 10 11 12 13

  CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name [ ( column_name [, ...] ) ] [ BUILD { DEFERRED | IMMEDIATE } ] [ REFRESH [ [ DEFAULT ] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE | FAST ] [ [ ON DEMAND ] | [ ON STATEMENT ] | [ ON COMMIT ] ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ] [ { ENABLE | DISABLE } QUERY REWRITE ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ] [ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ] AS query;

• Refresh a materialized view.

  1 2	REFRESH MATERIALIZED VIEW {[schema.]materialized_view_name} [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ]

• Drop a materialized view.

  1 2	DROP MATERIALIZED VIEW [ IF EXISTS ] {[schema.]materialized_view_name} [, ...] [ CASCADE | RESTRICT ];

• Alter a materialized view.

  1 2 3 4 5 6 7 8 9 10

  ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }[ ENABLE | DISABLE ] QUERY REWRITE; ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name } REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ]; ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name } REFRESH [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ; ALTER MATERIALIZED VIEW { materialized_view_name } OWNER TO new_owner; ALTER MATERIALIZED VIEW { materialized_view_name } SET ( {storage_parameter = value} [, ... ] ) | RESET ( storage_parameter [, ... ] );

For details about the parameters of the materialized view syntax, see CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW.

Scenarios of Materialized Views

Materialized views apply to the following scenarios:

• The query result does not change frequently, that is, the base table is stable and the execution frequency is high.
• The query involves time-consuming operations, such as aggregation and join. For queries involving multiple table join and aggregation operations, materialized views can pre-calculate and store these time-consuming operations to avoid re-executing these operations during each query.
• The query result involves only a small part of data in the table.

Required Permissions for Using Materialized Views

• To create a materialized view, you must have the CREATE permission on the schema and the SELECT permission on the base table or column.
• To query a materialized view, you must have the SELECT permission on the materialized view.
• To refresh a materialized view, you must have the INSERT, UPDATE, DELETE, and ANALYZE permissions on the materialized view and the SELECT permission on the base table or column.
• To delete a materialized view, you must have the DROP TABLE permission.

Restrictions on Materialized Views

• Temporary tables cannot be included, including global, volatile, and common temporary tables.
• The CTE in the definition of a materialized view cannot contain other DML statements except the SELECT statement.
• The base table of a materialized view cannot have data masking policies or row-level access control. It also cannot be owned by a private user. The usage of masking policies and row-level access control may lead to errors in the result set, while materialized views owned by private users may cause refresh failures.
• The stable and volatile functions are supported by clusters of version 9.1.0.200 and later. Materialized views cannot detect changes in function content. To ensure that function changes are reflected in the materialized view results in a timely manner, you need to execute REFRESH MATERIALIZED VIEW RESTRICT to refresh the materialized views or report the function changes to the materialized views with data updates.
• Materialized views cannot automatically detect changes in global variables or session parameters that affect their query execution. You need to refresh the materialized views to update the results.

Suggestions for Using Materialized Views

• Storage resources: Materialized views occupy extra storage space. Therefore, you need to consider the disk capacity.
• Refresh maintenance: Refreshing the materialized view, whether done automatically or manually, uses system resources. If the data in the base table changes often, the refresh process can impact system performance.
• Asynchronous materialized views do not update automatically when the original data changes. If the base table data is modified, the view data may become outdated. You need to periodically update the view data to ensure data consistency.
• Design complexity: When designing and creating materialized views, it is crucial to consider the expected query mode and data access mode to optimize performance.