Considerations
- When you drop a materialized view, it is not moved to the recycle bin.
- If materialized view logs have been created for the base table of a materialized view, you must first drop the materialized view logs on the base table before dropping the base table. Otherwise, an error occurs.
Privilege requirements
To drop a materialized view, you must have the DROP TABLE privilege. For more information about privileges in OceanBase AI Database, see Permission classification.
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
Parameters |
Description |
|---|---|
| IF EXISTS | Optional. If IF EXISTS is specified, an error is not returned if the materialized view to be dropped does not exist; otherwise, an error is returned if the materialized view to be dropped does not exist. |
| database. | Optional. Specifies the database where the materialized view is located. If database. is omitted, the current database is used by default. |
| materialized_view | Specifies the name of the materialized view. |
| RESTRICT | CASCADE | Optional. Specifies the behavior of the drop operation.
|
Examples
The following examples show how to create a materialized view, query a materialized view, and drop a materialized view.
Create a table named
test_tbl1as the base table for the materialized view.CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);Create a materialized view named
mv_test_tbl1based on thetest_tbl1table.CREATE MATERIALIZED VIEW mv_test_tbl1(PRIMARY KEY (col1)) AS SELECT col1, col2 FROM test_tbl1;Create materialized view logs for the materialized view
mv_test_tbl1.CREATE MATERIALIZED VIEW LOG ON mv_test_tbl1 WITH PRIMARY KEY (col2) INCLUDING NEW VALUES;Create a materialized view (nested materialized view) named
mv_mv_test_tbl1based on the materialized viewmv_test_tbl1.CREATE MATERIALIZED VIEW mv_mv_test_tbl1 AS SELECT col1 FROM mv_test_tbl1;Query all materialized views.
SELECT owner, mview_name, container_name, query, refresh_mode FROM oceanbase.DBA_MVIEWS;The query 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 setDrop the materialized view
mv_mv_test_tbl1.DROP MATERIALIZED VIEW mv_mv_test_tbl1;The query result is as follows:
Query OK, 0 rows affectedDrop the materialized view
mv_test_tbl1.Notice
If a materialized view log has been created on the base table of a materialized view, you must first drop the materialized view log on the base table before dropping the base table. Otherwise, an error will be returned.
DROP MATERIALIZED VIEW mv_test_tbl1;The query result is as follows:
ERROR 1235 (0A000): drop table required by materialized view refresh is not supportedDrop the materialized view log on the materialized view
mv_test_tbl1.DROP MATERIALIZED VIEW LOG ON mv_test_tbl1;The query result is as follows:
Query OK, 0 rows affectedDrop the materialized view
mv_test_tbl1again.DROP MATERIALIZED VIEW mv_test_tbl1;The query result is as follows:
Query OK, 0 rows affected