Working with Materialized Views
A materialized view is a pre - computed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is pre - computed, querying a materialized view is faster than executing a query…
SHOW MATERIALIZED VIEWS
Optionally filters the command output by object name. The filter uses case - insensitive pattern matching, with support for SQL wildcard characters (% and _).
CREATE MATERIALIZED VIEW
Creates a new materialized view in the current/specified schema, based on a query of an existing table, and populates the view with data.
Views, materialized views, and dynamic tables
Materialized views: Materialized views differ from traditional views by providing the ability to pre - compute the dataset based on materialized view query. Because the result is pre - computed, querying a materialized view is faster than…
ALTER MATERIALIZED VIEW
Alters a materialized view in the current/specified schema. Supported actions include: Renaming the materialized view. Suspending and resuming use and maintenance of the materialized view.
Materialized Views: Failed Refresh Invalidates a Materialized View
Materialized views are updated automatically on a regular basis by a background process. Currently, if the refresh of a materialized view fails, the data for the materialized view is not updated, and the background process continues to…
DESCRIBE MATERIALIZED VIEW
To see the materialized view’s definition, use SHOW MATERIALIZED VIEWS or GET_DDL. DESC MATERIALIZED VIEW and DESCRIBE TABLE are interchangeable. Either command retrieves the details for the table or view that matches the criteria in the…
Materialized Views: MINUS, EXCEPT, and INTERSECT No Longer Allowed
Materialized Views: MINUS, EXCEPT, and INTERSECT No Longer Allowed Attention This behavior change is in the 2023_04 bundle. For the current status of the bundle, refer to Bundle History. In the current release, you are not allowed to use…
DROP MATERIALIZED VIEW
Dropping a materialized view does not update references to that view. For example, if you create a view named “V1” on top of a materialized view, and then you drop the materialized view, the definition of view “V1” will become out…
TRUNCATE MATERIALIZED VIEW
Removes all rows from a materialized view, but leaves the view intact (including all privileges and constraints on the materialized view). Note that this is different from DROP MATERIALIZED VIEW, which removes the materialized view from…
Source