Drop a materialized view

Considerations

• When you drop a materialized view, it is not moved to the recycle bin.
• If materialized view logs are created on the base table of the materialized view, you must drop the materialized view logs on the base table before you drop the base table. Otherwise, an error is returned.

Privilege requirements

To drop a materialized view, you must have the DROP TABLE privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

DROP MATERIALIZED VIEW [IF EXISTS] materialized_view_list [opt_drop_behavior];

materialized_view_list:
    [ database. ]materialized_view [,[ database. ]materialized_view]...

opt_drop_behavior:
    RESTRICT | CASCADE

Parameters

Examples

The following examples show how to create a materialized view, query materialized views, and drop a materialized view.

1. Create a table named test_tbl1 as the base table of the materialized view.

   CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   

2. Create a materialized view named mv_test_tbl1 based on the test_tbl1 table.

   CREATE MATERIALIZED VIEW mv_test_tbl1(PRIMARY KEY (col1))
       AS SELECT col1, col2
          FROM test_tbl1;
   

3. Create a materialized view log on the mv_test_tbl1 materialized view.

   CREATE MATERIALIZED VIEW LOG ON mv_test_tbl1
       WITH PRIMARY KEY (col2) INCLUDING NEW VALUES;
   

4. Create a nested materialized view named mv_mv_test_tbl1 based on the mv_test_tbl1 materialized view.

   CREATE MATERIALIZED VIEW mv_mv_test_tbl1
       AS SELECT col1
          FROM mv_test_tbl1;
   

5. View information about all materialized views.

   SELECT owner, mview_name, container_name, query, refresh_mode
   FROM oceanbase.DBA_MVIEWS;
   

   The return result is as follows:

   +---------+-----------------+-----------------+-----------------------------------------------------------------------------------------------------------------+--------------+
   | owner   | mview_name      | container_name  | query                                                                                                           | refresh_mode |
   +---------+-----------------+-----------------+-----------------------------------------------------------------------------------------------------------------+--------------+
   | db_test | mv_mv_test_tbl1 | mv_mv_test_tbl1 | select `mv_test_tbl1`.`col1` AS `col1` from `db_test`.`mv_test_tbl1` `mv_test_tbl1`                             | DEMAND       |
   | db_test | mv_test_tbl1    | mv_test_tbl1    | select `db_test`.`test_tbl1`.`col1` AS `col1`,`db_test`.`test_tbl1`.`col2` AS `col2` from `db_test`.`test_tbl1` | DEMAND       |
   +---------+-----------------+-----------------+-----------------------------------------------------------------------------------------------------------------+--------------+
   2 rows in set
   

6. Drop the mv_mv_test_tbl1 materialized view.

   DROP MATERIALIZED VIEW mv_mv_test_tbl1;
   

   The return result is as follows:

   Query OK, 0 rows affected
   

7. Drop the mv_test_tbl1 materialized view.

   Notice

   If materialized view logs are created on the base table of the materialized view, you must drop the materialized view logs on the base table before you drop the base table. Otherwise, an error is returned.

   DROP MATERIALIZED VIEW mv_test_tbl1;
   

   The return result is as follows:

   ERROR 1235 (0A000): drop table required by materialized view refresh is not supported
   

8. Drop the materialized view logs on the mv_test_tbl1 materialized view.

   DROP MATERIALIZED VIEW LOG ON mv_test_tbl1;
   

   The return result is as follows:

   Query OK, 0 rows affected
   

9. Drop the mv_test_tbl1 materialized view again.

   DROP MATERIALIZED VIEW mv_test_tbl1;
   

   The return result is as follows:

   Query OK, 0 rows affected
   

References

• Overview of materialized views
• Materialized view logs
• Create a materialized view
• Refresh a materialized view
• Query rewrite by using materialized views
• Query materialized views