DROP MATERIALIZED VIEW

description

  1. This statement is used to delete a materialized view. Synchronization syntax

syntax:

  1. ```
  2. DROP MATERIALIZED VIEW [IF EXISTS] mv_name ON table_name
  3. ```

  1. IF EXISTS If the materialized view does not exist, doris will not throw an error. If this keyword is not declared, an error will be reported if the materialized view does not exist. Ranch

  2. mv_name The name of the materialized view to be deleted. Required.

  3. Table_name Name of the table to which the materialized view to be deleted belongs. Required.

example

Table structure is

  1. mysql> desc all_type_table all;
  2. +----------------+-------+----------+------+-------+---------+-------+
  3. | IndexName      | Field | Type     | Null | Key   | Default | Extra |
  4. +----------------+-------+----------+------+-------+---------+-------+
  5. | all_type_table | k1    | TINYINT  | Yes  | true  | N/A     |       |
  6. |                | k2    | SMALLINT | Yes  | false | N/A     | NONE  |
  7. |                | k3    | INT      | Yes  | false | N/A     | NONE  |
  8. |                | k4    | BIGINT   | Yes  | false | N/A     | NONE  |
  9. |                | k5    | LARGEINT | Yes  | false | N/A     | NONE  |
  10. |                | k6    | FLOAT    | Yes  | false | N/A     | NONE  |
  11. |                | k7    | DOUBLE   | Yes  | false | N/A     | NONE  |
  12. |                |       |          |      |       |         |       |
  13. | k1_sumk2       | k1    | TINYINT  | Yes  | true  | N/A     |       |
  14. |                | k2    | SMALLINT | Yes  | false | N/A     | SUM   |
  15. +----------------+-------+----------+------+-------+---------+-------+

  1. Drop the materialized view named k1_sumk2 of the table all_type_table

    1. drop materialized view k1_sumk2 on all_type_table;

    Table structure after materialized view is deleted as following:

+————————+———-+—————+———+———-+————-+———-+ | IndexName | Field | Type | Null | Key | Default | Extra | +————————+———-+—————+———+———-+————-+———-+ | all_type_table | k1 | TINYINT | Yes | true | N/A | | | | k2 | SMALLINT | Yes | false | N/A | NONE | | | k3 | INT | Yes | false | N/A | NONE | | | k4 | BIGINT | Yes | false | N/A | NONE | | | k5 | LARGEINT | Yes | false | N/A | NONE | | | k6 | FLOAT | Yes | false | N/A | NONE | | | k7 | DOUBLE | Yes | false | N/A | NONE | +————————+———-+—————+———+———-+————-+———-+ ```

  1. Delete a non-existing materialized view in the table all_type_table

    1. drop materialized view k1_k2 on all_type_table;

ERROR 1064 (HY000): errCode = 2, detailMessage = Materialized view [k1_k2] does not exist in table [all_type_table] ```

  1. The delete request directly reports an error

  1. Delete the materialized view k1_k2 in the table all_type_table. Materialized view does not exist and no error is reported.

    1. drop materialized view if exists k1_k2 on all_type_table;

Query OK, 0 rows affected (0.00 sec) ```

  1. If it exists, it will be deleted; If it does not exist, no error will be reported.

keyword

DROP, MATERIALIZED, VIEW