The materialized view synopsis differs a little bit from the normal view synopsis. Materialized views are a PostgreSQL extension, but several databases, such as Oracle, support it. As shown in the following synopsis, a materialized view can be created in a certain TABLESPACE, as well as storage_parameter, which is logical since materialized views are physical objects:
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]
At the time of creation of a materialized view, it can be populated with data or left empty. If it is not populated, retrieving data from the unpopulated materialized view will raise an ERROR. The REFRESH MATERIALIZED VIEW statement can be used to populate a materialized view. The synopsis for the refresh command is as follows:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ]
The following example shows an attempt to retrieve data from an unpopulated materialized view:
car_portal=> CREATE MATERIALIZED VIEW test_mat AS SELECT 1 WITH NO DATA;
CREATE MATERIALIZED VIEW
car_portal=> TABLE test_mat;
ERROR: materialized view "test_mat" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
To refresh the view, as the hint suggested, one needs to refresh the view as follows:
car_portal=> REFRESH MATERIALIZED VIEW test_mat;
REFRESH MATERIALIZED VIEW
car_portal=> TABLE test_mat;
?column?
----------
1
(1 row)
Materialized views are often used with data warehousing. In data warehousing, several queries are required for business analysis and decision support. The data in these kinds of applications does not usually change, but the calculation and aggregation of that data is often a costly operation. In general, a materialized view can be used for the following:
- Generating summary reports
- Caching the results of recurring queries
- Optimizing performance by processing data only once
Since materialized views are tables, they can also be indexed, leading to a great performance boost.