This topic describes how to query a materialized view.
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 [test]> 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 `test`.`tbl1`.`id` AS `id`,`test`.`tbl1`.`name` AS `name` from `test`.`tbl1` where (`test`.`tbl1`.`age` >= 20) | utf8mb4 | utf8mb4_general_ci |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
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 |
|---|---|
| DBA_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 oceanbase.DBA_MVIEWS;
The return result is as follows:
+---------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| owner | mview_name | container_name | query | query_len |
+---------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| test_db | mv_tbl1_tbl2 | mv_tbl1_tbl2 | select `t1`.`id` AS `id`,`t1`.`name` AS `name`,`t2`.`notes` AS `notes` from `test_db`.`tbl1` `t1`,`test_db`.`tbl2` `t2` where (`t1`.`id` = `t2`.`tbl1_id`) | 154 |
| test_db | mv3_tbl1 | mv3_tbl1 | select `test_db`.`tbl1`.`id` AS `id`,`test_db`.`tbl1`.`name` AS `name` from `test_db`.`tbl1` where (`test_db`.`tbl1`.`age` >= 20) | 129 |
| test_db | mv2_tbl1 | mv2_tbl1 | select `test_db`.`tbl1`.`id` AS `id`,`test_db`.`tbl1`.`name` AS `name` from `test_db`.`tbl1` where (`test_db`.`tbl1`.`age` >= 20) | 129 |
| test_db | mv1_tbl1 | mv1_tbl1 | select `test_db`.`tbl1`.`id` AS `id`,`test_db`.`tbl1`.`name` AS `name` from `test_db`.`tbl1` where (`test_db`.`tbl1`.`age` >= 20) | 129 |
| test_db | mv_tbl3 | mv_tbl3 | select `test_db`.`tbl3`.`col2` AS `col2`,count(*) AS `cnt`,count(`test_db`.`tbl3`.`col3`) AS `cnt_col3`,sum(`test_db`.`tbl3`.`col3`) AS `sum_col3` from `test_db`.`tbl3` group by `test_db`.`tbl3`.`col2` | 201 |
| test_db | mv_tbl1 | mv_tbl1 | select `test_db`.`tbl1`.`id` AS `id`,`test_db`.`tbl1`.`name` AS `name` from `test_db`.`tbl1` where (`test_db`.`tbl1`.`age` >= 20) | 129 |
+---------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
6 rows in set