Creating and Managing Materialized Views
Materialized views are similar to views. A materialized view enables you to save a frequently used or complex query, then access the query results in a
SELECT statement as if they were a table. Materialized views persist the query results in a table-like form. While access to the data stored in a materialized view can be much faster than accessing the underlying tables directly or through a view, the data is not always current.
The materialized view data cannot be directly updated. To refresh the materialized view data, use the
REFRESH MATERIALIZED VIEW command. The query used to create the materialized view is stored in exactly the same way that a view’s query is stored. For example, you can create a materialized view that quickly displays a summary of historical sales data for situations where having incomplete data for the current date would be acceptable.
CREATE MATERIALIZED VIEW sales_summary AS SELECT seller_no, invoice_date, sum(invoice_amt)::numeric(13,2) as sales_amt FROM invoice WHERE invoice_date < CURRENT_DATE GROUP BY seller_no, invoice_date ORDER BY seller_no, invoice_date; CREATE UNIQUE INDEX sales_summary_seller ON sales_summary (seller_no, invoice_date);
The materialized view might be useful for displaying a graph in the dashboard created for sales people. You could schedule a job to update the summary information each night using this command.
REFRESH MATERIALIZED VIEW sales_summary;
The information about a materialized view in the Greenplum Database system catalogs is exactly the same as it is for a table or view. A materialized view is a relation, just like a table or a view. When a materialized view is referenced in a query, the data is returned directly from the materialized view, just like from a table. The query in the materialized view definition is only used for populating the materialized view.
If you can tolerate periodic updates of materialized view data, the performance benefit can be substantial.
One use of a materialized view is to allow faster access to data brought in from an external data source such as external table or a foreign data wrapper. Also, you can define indexes on a materialized view, whereas foreign data wrappers do not support indexes; this advantage might not apply for other types of external data access.
If a subquery is associated with a single query, consider using the
WITH clause of the
SELECT command instead of creating a seldom-used materialized view.
Parent topic:Defining Database Objects
CREATE MATERIALIZED VIEWcommand defines a materialized view based on a query.
CREATE MATERIALIZED VIEW us_users AS SELECT u.id, u.name, a.zone FROM users u, address a WHERE a.country = 'USA';
If a materialized view query contains an
ORDER BY or
SORT clause, the clause is ignored when a
SELECT is performed on the materialized query.
REFRESH MATERIALIZED VIEW command updates the materialized view data.
REFRESH MATERIALIZED VIEW us_users;
WITH NO DATA clause, the current data is removed, no new data is generated, and the materialized view is left in an unscannable state. An error is returned if a query attempts to access an unscannable materialized view.
REFRESH MATERIALIZED VIEW us_users WITH NO DATA;
DROP MATERIALIZED VIEW command removes a materialized view definition and data. For example:
DROP MATERIALIZED VIEW us_users;
DROP MATERIALIZED VIEW ... CASCADE command also removes all dependent objects. For example, if another materialized view depends on the materialized view which is about to be dropped, the other materialized view will be dropped as well. Without the
CASCADE option, the
DROP MATERIALIZED VIEW command fails.