DROP MATERIALIZED VIEW

Use the DROP MATERIALIZED VIEW command to remove a materialized view from the database.

Before you can remove a materialzied view, you must remove all its dependent materialzied views.

Syntax

DROP MATERIALIZED VIEW [ IF EXISTS ] [schema_name.]mv_name [ CASCADE ];

Parameters

Parameter	Description
schema_name	Specify the name of a schema to remove the materialized view in that schema. You can use SHOW SCHEMAS to get a list of all available schemas. If you don’t specify a schema, the specified materialized view in the default schema public will be removed.
mv_name	The name of the materialized view to remove. You can use SHOW MATERIALIZED VIEWS to get a list of all available materialized views.
CASCADE option	If this option is specified, all objects (such as other materialized views or regular views) that depend on the materialized view, and in turn all objects that depend on those objects will be dropped.

Examples

This statement removes the ad_ctr_5min materialized view in the default schema (public) from the database:

DROP MATERIALIZED VIEW ad_ctr_5min;

This statement removes the ad_ctr_5min materialized view in the rw_schema schema from the database:

DROP MATERIALIZED VIEW IF EXISTS rw_schema.ad_ctr_5min;

Hanging or long running DROP MATERIALIZED VIEW

If your DROP MATERIALIZED VIEW command takes a long time to run, it could be due to high barrier latency. DROP commands need to be synchronized with barriers currently. To let DROP take effect immediately, you can use the RECOVER command in a separate session.