This topic describes how to query materialized views.
View the definition of a materialized view
You can use the SHOW CREATE TABLE statement to query the definition of a materialized view.
Here is an example:
Query the definition of a materialized view named mv_tbl1.
obclient [SYS]> SHOW CREATE TABLE mv_tbl1;
The return result is as follows:
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| 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
View the information about materialized views by using system views
The following table describes system views that you can query to view the information about materialized views.
View name |
Description |
|---|---|
| ALL_MVIEWS | Displays the information about materialized views. |
| DBA_MVREF_STATS_SYS_DEFAULTS | Displays all default values of the refresh history statistics properties for materialized views in the entire system. |
| DBA_MVREF_STATS_PARAMS | Displays the refresh statistics properties associated with each materialized view. |
| DBA_MVREF_RUN_STATS | Displays the information about each refresh of the materialized views. Each refresh is identified by a REFRESH_ID. |
| DBA_MVREF_STATS | Displays the basic timing statistics of materialized view refreshes. |
| DBA_MVREF_CHANGE_STATS | Displays refresh statistics of materialized views. |
| DBA_MVREF_STMT_STATS | Displays the information about the refresh statements of materialized views. |
Here is an example:
SELECT owner, mview_name, container_name, query, query_len FROM sys.DBA_MVIEWS;
The return result is as follows:
+---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| 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