# SHOW MATERIALIZED VIEWS #### 语法 SHOW MATERIALIZED VIEWS \[IN/FROM schema_name\] \[WITH TABLES LIKE pattern\] #### 描述 列出catalogName为mv中的所有物化视图以及对应的数据表。如果希望只查看某个schema中的物化视图，可以使用子句\[IN/FROM schema_name\] 通过可选子句"LIKE"，筛选视图名满足规则运算表达式的视图，如果不使用这个子句，会列举所有视图。匹配的视图会按字母顺序排列。 目前规则运算表达式支持"\*"或"%"用于匹配任何字符，下划线"_"用于匹配一个字符，或"\|"用于条件连接两个或多个条件。 #### 示例 - SHOW MATERIALIZED VIEWS; ``` hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS; Materialized Views | Last Refresh Time | Status | State | SyncStatus ---|---|---|---|------------ mv.auto_created_mv.auto_created_mv_20220906_201815_mv_1 | Wed Sep 07 15:37:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_103 | Wed Sep 07 15:31:41 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_15 | Wed Sep 07 14:47:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_16 | Wed Sep 07 15:39:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_18 | Wed Sep 07 14:46:42 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_23 | Wed Sep 07 15:39:49 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_24 | Wed Sep 07 14:48:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_28 | Wed Sep 07 15:38:38 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_29 | Wed Sep 07 15:38:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_30 | Wed Sep 07 15:38:04 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_31 | Wed Sep 07 15:39:28 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_33 | Wed Sep 07 15:39:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_34 | Wed Sep 07 14:47:38 CST 2022 | ENABLE | Stale | true ``` ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) - Materialized Views：物化视图的名称 - Last Refresh Time：最近一次刷新物化视图的时间 - Status：物化视图状态 - DISABLE：物化视图连续三次自动刷新失败导致的不可用状态，不可用作被改写 - ENABLE：正常状态 - REFRESHING：刷新中 - INIT：物化视图首次被创建，没有实体数据 - SUSPEND：挂起状态，不能改写，不能刷新 - State：物化视图有效期 - Stale：物化视图过期 - Valid：物化视图未过期，正常状态 - SyncStatus：物化视图缓存是否同步 - SHOW MATERIALIZED VIEWS FROM tpcds; ``` hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS FROM auto_created_mv; Materialized Views | Last Refresh Time | Status | State | SyncStatus ---|---|---|---|------------ mv.auto_created_mv.auto_created_mv_20220906_201815_mv_1 | Wed Sep 07 15:37:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_103 | Wed Sep 07 15:31:41 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_15 | Wed Sep 07 14:47:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_16 | Wed Sep 07 15:39:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_18 | Wed Sep 07 14:46:42 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_23 | Wed Sep 07 15:39:49 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_24 | Wed Sep 07 14:48:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_28 | Wed Sep 07 15:38:38 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_29 | Wed Sep 07 15:38:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_30 | Wed Sep 07 15:38:04 CST 2022 | ENABLE | Stale | true ``` - SHOW MATERIALIZED VIEWS WITH TABLES LIKE 'hive.tpcds_bin_partitioned_orc_2.call_center'; ``` hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE 'hive.tpcds_bin_partitioned_orc_2.call_center'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ---|---|---|---|---|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true ``` - SHOW MATERIALIZED VIEWS WITH TABLES LIKE 'hive.tpcds_bin_partitioned_orc_2.call_center'; ``` hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE 'hive.tpcds_bin_partitioned_orc_2.call_center'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ---|---|---|---|---|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true ``` - SHOW MATERIALIZED VIEWS TABLES LIKE '\*.call_center'; ``` hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE '*.call_center'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ---|---|---|---|---|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true ``` - SHOW MATERIALIZED VIEWS WITH TABLES LIKE '\*.**call_center** \|\*.date_dim'; ``` hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE '*.call_center|*.date_dim'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ---|---|---|---|---|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_1 | hive.tpcds_bin_partitioned_orc_2.date_dim | Wed Sep 07 15:37:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_103| hive.tpcds_bin_partitioned_orc_2.date_dim | Wed Sep 07 15:31:41 CST 2022 | ENABLE | Stale | true ```