Materialized Views
Tags: opengauss
Published: 2023-03-07 06:57:47

A materialized view is relative to a common view. A common view is a virtual table, and a materialized view stores the execution results of SQL statements. You can directly use data without repeatedly executing query statements, improving performance.

Materialized views are classified into the following types based on the refresh mode:

**·** Full-refresh materialized view: Only created materialized views can be fully refreshed. Incremental refresh is not supported. The syntax for creating a full-refresh materialized view is similar to the **CREATE TABLE AS** syntax.
**·** Incremental-refresh materialized view: Materialized views can be incrementally refreshed. You need to manually execute statements to incrementally refresh materialized views in a period of time. The difference between the incremental-refresh and the full-refresh materialized views is that the incremental-refresh materialized view supports only a small number of scenarios. Currently, only base table scanning statements or UNION ALL can be used to create materialized views.

**Syntax of a Full-Refresh Materialized View**
**·** Create a full-refresh materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gvnldomks5ozk479377i.png)

**·** Fully refresh a materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e5ijmb0p8lbl61pb5jrh.png)

**·** Delete a materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vuxrsh3721tlkcyvx8th.png)

**·** Query a materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xao8qoutwcljvs32fe9c.png)

**Parameters for Creating a Full-Refresh Materialized View**
**·** view_name

Specifies the name of the materialized view to be created.

**·** AS query

Specifies the **SELECT, TABLE**, or **VALUES** command.

**Example of Creating a Full-Refresh Materialized View**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y45vtd0c7j8ip1k8vq60.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/53mlvmxz2agiw9y6e1sv.png)

**Syntax of an Incremental-Refresh Materialized View**
**·** Create an incremental-refresh materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vv0da3xqykckrtxzafqm.png)

**·** Fully refresh a materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7j85j7iv1mtmdwzugkrd.png)

**·** Incrementally refresh a materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wmvs3159ke91chskoe9j.png)

**·** Delete a materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ll9w9fk6l80q4qogaht2.png)

**·** Query a materialized view.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y3w87diw01er9bro08qk.png)

**Parameters for Creating an Incremental-Refresh Materialized View**
**· view_name**

Specifies the name of the materialized view to be created.

**· AS query**

Specifies the **SELECT, TABLE**, or **VALUES** command.

**Example of Creating an Incremental-Refresh Materialized View**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l8fwukxjimet74173g36.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bcuuz7v537vxprfqs58y.png)