REFRESH MATERIALIZED VIEW - Amazon Redshift

REFRESH MATERIALIZED VIEW

Refreshes a materialized view.

When you create a materialized view, its contents reflect the state of the underlying database table or tables at that time. The data in the materialized view remains unchanged, even when applications make changes to the data in the underlying tables. To update the data in a materialized view, you can use the REFRESH MATERIALIZED VIEW statement at any time. When you use this statement, Amazon Redshift identifies changes that have taken place in the base table or tables, and then applies those changes to the materialized view.

For more information about materialized views, see Creating materialized views in Amazon Redshift.

Syntax

REFRESH MATERIALIZED VIEW mv_name

Parameters

mv_name

The name of the materialized view to be refreshed.

Usage notes

Only the owner of a materialized view can perform a REFRESH MATERIALIZED VIEW operation on that materialized view. Furthermore, the owner must have SELECT privilege on the underlying base tables to successfully run REFRESH MATERIALIZED VIEW.

The REFRESH MATERIALIZED VIEW command runs as a transaction of its own. Amazon Redshift transaction semantics are followed to determine what data from base tables is visible to the REFRESH command, or when the changes made by the REFRESH command are made visible to other transactions running in Amazon Redshift.

  • For incremental materialized views, REFRESH MATERIALIZED VIEW uses only those base table rows that are already committed. Therefore, if the refresh operation runs after a data manipulation language (DML) statement in the same transaction, then changes of that DML statement aren't visible to refresh.

  • For a full refresh of a materialized view, REFRESH MATERIALIZED VIEW sees all base table rows visible to the refresh transaction, according to usual Amazon Redshift transaction semantics.

  • Depending on the input argument type, Amazon Redshift still supports incremental refresh for materialized views for the following functions with specific input argument types: DATE (timestamp), DATE_PART (date, time, interval, time-tz), DATE_TRUNC (timestamp, interval).

Some operations in Amazon Redshift interact with materialized views. Some of these operations might force a REFRESH MATERIALIZED VIEW operation to fully recompute the materialized view even though the query defining the materialized view only uses the SQL features eligible for incremental refresh. For example:

  • Background vacuum operations might be blocked if materialized views aren't refreshed. After an internally defined threshold period, a vacuum operation is allowed to run. When this vacuum operation happens, any dependent materialized views are marked for recomputation upon the next refresh (even if they are incremental). For information about VACUUM, see VACUUM. For more information about events and state changes, see STL_MV_STATE.

  • Some user-initiated operations on base tables force a materialized view to be fully recomputed next time that a REFRESH operation is run. Examples of such operations are a manually invoked VACUUM, a classic resize, an ALTER DISTKEY operation, an ALTER SORTKEY operation, and a truncate operation. For more information about events and state changes, see STL_MV_STATE.

Limitations for incremental refresh

Amazon Redshift currently doesn't support incremental refresh for materialized views that are defined with a query using any of the following SQL elements:

  • OUTER JOIN (RIGHT, LEFT, or FULL).

  • Set operations: UNION, INTERSECT, EXCEPT, MINUS.

  • UNION ALL when it occurs in a subquery and an aggregate function or a GROUP BY clause is present in the query.

  • Aggregate functions: AVG, MEDIAN, PERCENTILE_CONT, LISTAGG, STDDEV_SAMP, STDDEV_POP, APPROXIMATE COUNT, APPROXIMATE PERCENTILE, and bitwise aggregate functions.

    Note

    The COUNT and SUM aggregate functions are supported.

  • DISTINCT aggregate functions, such as DISTINCT COUNT, DISTINCT SUM, and so on.

  • Window functions.

  • A query that uses temporary tables for query optimization, such as optimizing common subexpressions.

  • Subqueries in any place other than the FROM clause.

  • External tables referenced as base tables in the query that defines the materialized view.

  • Mutable functions, such as date-time functions, RANDOM and non-STABLE user-defined functions.

Examples

The following example refreshes the tickets_mv materialized view.

REFRESH MATERIALIZED VIEW tickets_mv;