DROP MATERIALIZED VIEW

Description

Drops a materialized view. You cannot drop a materialized view that is being created in process with this command. To drop a materialized view that is being created in process, see Materialized View for further instructions.

CAUTION

Only users with the DROP_PRIV privilege in the database where the base table resides can drop a materialized view.

Syntax

DROP MATERIALIZED VIEW [IF EXISTS] [database.]mv_name;

Parameters in brackets [] is optional.

Parameters

ParameterRequiredDescription
IF EXISTSnoIf this parameter is specified, CelerData will not throw an exception when deleting a materialized view that does not exist. If this parameter is not specified, the system will throw an exception when deleting a materialized view that does not exist.
mv_nameyesThe name of the materialized view to delete.

Examples

Example 1: Drop an existing materialized view

  1. View all existing materialized views in the database.
  MySQL > SHOW MATERIALIZED VIEW\G
  *************************** 1. row ***************************
              id: 470740
          name: order_mv1
  database_name: default_cluster:sr_hub
        text: SELECT `sr_hub`.`orders`.`dt` AS `dt`, `sr_hub`.`orders`.`order_id` AS `order_id`, `sr_hub`.`orders`.`user_id` AS `user_id`, sum(`sr_hub`.`orders`.`cnt`) AS `total_cnt`, sum(`sr_hub`.`orders`.`revenue`) AS `total_revenue`, count(`sr_hub`.`orders`.`state`) AS `state_count` FROM `sr_hub`.`orders` GROUP BY `sr_hub`.`orders`.`dt`, `sr_hub`.`orders`.`order_id`, `sr_hub`.`orders`.`user_id`
          rows: 0
  1 rows in set (0.00 sec)
  1. Drop the materialized view order_mv1.
  DROP MATERIALIZED VIEW order_mv1;
  1. Check if the dropped materialized view exists.
  MySQL > SHOW MATERIALIZED VIEW;
  Empty set (0.01 sec)

Example 2: Drop a non-existing materialized view

  • If the parameter IF EXISTS is specified, CelerData will not throw an exception when deleting a materialized view that does not exist.
MySQL > DROP MATERIALIZED VIEW IF EXISTS k1_k2;
Query OK, 0 rows affected (0.00 sec)
  • If the parameter IF EXISTS is not specified, the system will throw an exception when deleting a materialized view that does not exist.
MySQL > DROP MATERIALIZED VIEW k1_k2;
ERROR 1064 (HY000): Materialized view k1_k2 is not find
In this article
Copyright © 2024 CelerData