--- title: "查询物化视图 - OceanBase 数据库 V4.3.2 | OceanBase 文档中心" description: "查询物化视图 本节主要介绍物化视图的查看方法。 查看物化视图定义 使用 SHOW CREATE TABLE 语句查看物化视图定义。 示例如下： 查看物化视图 mv_tbl1 的定义。 obclient [SYS]> SHOW CREATE TABLE mv_tbl1; 返回结果如下： +---------+-----…" --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.3.2 # 查询物化视图 更新时间：2024-08-09 23:00:00 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.3.2/zh-CN/700.reference/300.database-object-management/200.manage-object-of-oracle-mode/500.manage-views-of-oracle-mode/200.manage-materialized-views-of-oracle-mode/100.materialized-views-of-oracle-mode/300.view-materialized-views-of-oracle-mode.md) 本节主要介绍物化视图的查看方法。 ## 查看物化视图定义 使用 `SHOW CREATE TABLE` 语句查看物化视图定义。 **示例如下：** 查看物化视图 `mv_tbl1` 的定义。 ```sql obclient [SYS]> SHOW CREATE TABLE mv_tbl1; ``` 返回结果如下： ```shell +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | VIEW | CREATE VIEW | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | MV_TBL1 | CREATE MATERIALIZED VIEW "MV_TBL1" AS select "SYS"."TBL1"."ID" AS "ID","SYS"."TBL1"."NAME" AS "NAME" from "SYS"."TBL1" where ("SYS"."TBL1"."AGE" >= 20) | utf8mb4 | utf8mb4_bin | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set ``` ## 使用视图查看物化视图信息 可以通过系统视图来查看物化视图的相关信息，可查看的视图有： | **视图名** | **功能描述** | | --- | --- | | [ALL_MVIEWS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001051284) | 展示物化视图信息。 | | [DBA_MVREF_STATS_SYS_DEFAULTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001051267) | 物化视图刷新历史统计属性的系统范围默认值。 | | [DBA_MVREF_STATS_PARAMS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001051314) | 展示与每个物化视图关联的刷新统计信息属性。 | | [DBA_MVREF_RUN_STATS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001051301) | 展示物化视图的每次刷新运行的信息，每次运行均由 REFRESH_ID 标识。 | | [DBA_MVREF_STATS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001051113) | 展示物化视图刷新的基本计时统计信息。 | | [DBA_MVREF_CHANGE_STATS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001051266) | 展示物化视图刷新相关的统计信息。 | | [DBA_MVREF_STMT_STATS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001051357) | 展示刷新语句关联的信息。 | **示例如下：** ```sql SELECT owner, mview_name, container_name, query, query_len FROM sys.DBA_MVIEWS; ``` 返回结果如下： ```shell +---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ | OWNER | MVIEW_NAME | CONTAINER_NAME | QUERY | QUERY_LEN | +---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ | SYS | MV_TBL1_TBL2 | __mv_container_500214 | select "T1"."ID" AS "ID","T1"."NAME" AS "NAME","T2"."NOTES" AS "NOTES" from "SYS"."TBL1" "T1","SYS"."TBL2" "T2" where ("T1"."ID" = "T2"."TBL1_ID") | 146 | | SYS | MV3_TBL1 | __mv_container_500211 | select "SYS"."TBL1"."ID" AS "ID","SYS"."TBL1"."NAME" AS "NAME" from "SYS"."TBL1" where ("SYS"."TBL1"."AGE" >= 20) | 113 | | SYS | MV2_TBL1 | __mv_container_500208 | select "SYS"."TBL1"."ID" AS "ID","SYS"."TBL1"."NAME" AS "NAME" from "SYS"."TBL1" where ("SYS"."TBL1"."AGE" >= 20) | 113 | | SYS | MV1_TBL1 | __mv_container_500205 | select "SYS"."TBL1"."ID" AS "ID","SYS"."TBL1"."NAME" AS "NAME" from "SYS"."TBL1" where ("SYS"."TBL1"."AGE" >= 20) | 113 | | SYS | MV_TBL3 | __mv_container_500202 | select "SYS"."TBL3"."COL2" AS "COL2",count(*) AS "CNT",count("SYS"."TBL3"."COL3") AS "CNT_COL3",sum("SYS"."TBL3"."COL3") AS "SUM_COL3" from "SYS"."TBL3" group by "SYS"."TBL3"."COL2" | 181 | | SYS | MV_TBL1 | __mv_container_500179 | select "SYS"."TBL1"."ID" AS "ID","SYS"."TBL1"."NAME" AS "NAME" from "SYS"."TBL1" where ("SYS"."TBL1"."AGE" >= 20) | 113 | | USER001 | MV_TEST_TBL1 | __mv_container_500154 | select "USER001"."TEST_TBL1"."COL1" AS "COL1","USER001"."TEST_TBL1"."COL2" AS "COL2","USER001"."TEST_TBL1"."COL3" AS "COL3" from "USER001"."TEST_TBL1" where ("USER001"."TEST_TBL1"."COL3" >= 30) | 193 | +---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ 7 rows in set ``` 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线