This topic describes how to drop a materialized view by using SQL statements.
Required privileges
You need the DROP TABLE privilege to drop a materialized view. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.
Considerations
A materialized view that is separately dropped is not moved to the recycle bin.
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
Parameter |
Description |
|---|---|
| IF EXISTS | Specifies whether to check the existence of the materialized view to be dropped. If you specify IF EXISTS and the materialized view to be dropped does not exist, the system does not report an error during execution. If you do not specify this parameter and the materialized view to be dropped does not exist, the system reports an error during execution. This parameter is optional. |
| database. | The name of the database in which the materialized view is located. This parameter is optional. If you do not specify the database. parameter, the database in which you execute the statement is used by default. |
| materialized_view | The name of the materialized view. |
| RESTRICT | CASCADE | The drop behavior. This parameter is optional.
|
Example
Drop the mv_test_tbl1 materialized view in the test_db database.
obclient [test]> DROP MATERIALIZED VIEW test_db.mv_test_tbl1;
The return result is as follows:
Query OK, 0 rows affected