Drop a materialized view

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 Oracle-compatible mode.

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.

Syntax

The syntax for dropping a materialized view is as follows:

DROP MATERIALIZED VIEW [ schema. ] materialized_view;

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 VARCHAR2(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 sys.DBA_MVIEWS
   WHERE OWNER = 'TEST_USER001';
   

   Notice

   In Oracle mode, the OWNER column of the view sys.DBA_MVIEWS matches the schema name in uppercase letters.

   The return result is as follows:

   +--------------+-----------------+-----------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+
   | OWNER        | MVIEW_NAME      | CONTAINER_NAME  | QUERY                                                                                                                          | REFRESH_MODE |
   +--------------+-----------------+-----------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+
   | TEST_USER001 | MV_TEST_TBL1    | MV_TEST_TBL1    | select "TEST_USER001"."TEST_TBL1"."COL1" AS "COL1","TEST_USER001"."TEST_TBL1"."COL2" AS "COL2" from "TEST_USER001"."TEST_TBL1" | DEMAND       |
   | TEST_USER001 | MV_MV_TEST_TBL1 | MV_MV_TEST_TBL1 | select "MV_TEST_TBL1"."COL1" AS "COL1" from "TEST_USER001"."MV_TEST_TBL1" "MV_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:

   OBE-00600: internal error code, arguments: -4007, 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