Creating materialized views in Amazon Redshift - Amazon Redshift

Creating materialized views in Amazon Redshift

In a data warehouse environment, applications often need to perform complex queries on large tables—for example, SELECT statements that perform multiple-table joins and aggregations on tables that contain billions of rows. Processing these queries can be expensive, in terms of system resources and the time it takes to compute the results.

Materialized views in Amazon Redshift provide a way to address these issues. A materialized view contains a precomputed result set, based on an SQL query over one or more base tables. You can issue SELECT statements to query a materialized view, in the same way that you can query other tables or views in the database. Amazon Redshift returns the precomputed results from the materialized view, without having to access the base tables at all. From the user standpoint, the query results are returned much faster compared to when retrieving the same data from the base tables.

Materialized views are especially useful for speeding up queries that are predictable and repeated. Instead of performing resource-intensive queries against large database tables (such as aggregates or multiple-table joins), applications can query a materialized view and retrieve a precomputed result set. For example, consider the scenario where a set of queries is used to populate a collection of charts, such as Amazon QuickSight. This use case is ideal for a materialized view, because the queries are predictable and repeated over and over again.

When you create a materialized view, Amazon Redshift runs the user-specified SQL statement to gather the data from the base table or tables and stores the result set. The following illustration provides an overview of the materialized view tickets_mv that an SQL query defines using two base tables events and sales.

For information on how to create materialized views, see CREATE MATERIALIZED VIEW.

You can issue SELECT statements to query a materialized view. For information on how to query materialized views, see Querying a materialized view.

The result set eventually becomes stale when data is inserted, updated, and deleted in the base tables. You can refresh the materialized view at any time to update it with the latest changes from the base tables. For information on how to refresh materialized views, see REFRESH MATERIALIZED VIEW.

For details about SQL commands used to create and manage materialized views, see the following command topics:

For information about system tables and views to monitor materialized views, see the following topics: