This topic describes how to query materialized views.
Query the definition of a materialized view
You can execute the SHOW CREATE TABLE statement to query the definition of a materialized view.
Here is an example:
Query the definition of the materialized view mv_tbl1.
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" ("COL1", "COL2") COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 REFRESH FORCE ON DEMAND AS select "TEST_USER001"."TBL1"."COL1" AS "COL1","TEST_USER001"."TBL1"."COL2" AS "COL2" from "TEST_USER001"."TBL1" where ("TEST_USER001"."TBL1"."COL3" >= 20) | utf8mb4 | utf8mb4_bin |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set
Query information about materialized views by using views
You can query information about materialized views by using the following views:
| View | Description |
|---|---|
| ALL_MVIEWS | Displays information about materialized views. |
| DBA_MVREF_STATS_SYS_DEFAULTS | Displays the system default values of statistics attributes for materialized view refreshes. |
| DBA_MVREF_STATS_PARAMS | Displays the refresh statistics attributes associated with each materialized view. |
| DBA_MVREF_RUN_STATS | Displays information about each refresh run of a materialized view. Each run is identified by a REFRESH_ID. |
| DBA_MVREF_STATS | Displays basic timing statistics for materialized view refreshes. |
| DBA_MVREF_CHANGE_STATS | Displays statistics about materialized view refreshes. |
| DBA_MVREF_STMT_STATS | Displays information about refresh statements. |
| DBA_SCHEDULER_JOBS | Displays information about all scheduled jobs in the database. |
Here is an example:
Query the background refresh task of the automatically refreshed materialized view mv_rc_swn_tbl1 by using the DBA_SCHEDULER_JOBS view.
SELECT JOB_NAME,JOB_ACTION,START_DATE,REPEAT_INTERVAL,LAST_START_DATE,NEXT_RUN_DATE
FROM sys.DBA_SCHEDULER_JOBS
WHERE JOB_ACTION like '%MV_RC_SWN_TBL1%';
Notice
In Oracle mode, the sys.DBA_SCHEDULER_JOBS view contains a column named JOB_ACTION. When you query the column for a table name, the table name must be in uppercase letters.
The return result is as follows:
+----------------------------------+--------------------------------------------------------------------------+-------------------------------------+----------------------------------+-------------------------------------+-------------------------------------+
| JOB_NAME | JOB_ACTION | START_DATE | REPEAT_INTERVAL | LAST_START_DATE | NEXT_RUN_DATE |
+----------------------------------+--------------------------------------------------------------------------+-------------------------------------+----------------------------------+-------------------------------------+-------------------------------------+
| MVIEW_REFRESH$J_1125899909162864 | DBMS_MVIEW.refresh('TEST_USER001.MV_RC_SWN_TBL1', refresh_parallel => 1) | 18-DEC-24 05.04.48.000000 PM +08:00 | current_date + INTERVAL '1' HOUR | 18-DEC-24 05.04.51.502947 PM +08:00 | 18-DEC-24 06.04.51.000000 PM +08:00 |
+----------------------------------+--------------------------------------------------------------------------+-------------------------------------+----------------------------------+-------------------------------------+-------------------------------------+
1 row in set