Note
This view is available starting with V4.3.0.
Purpose
The CDB_MVREF_STATS view displays the basic timing statistics of a materialized view refresh.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| MV_OWNER | varchar(128) | NO | The owner of the materialized view. |
| MV_NAME | varchar(128) | NO | The name of the materialized view. |
| REFRESH_ID | bigint(20) | NO | The ID of the refresh. |
| REFRESH_METHOD | varchar(30) | NO | The method used to refresh the materialized view.
|
| REFRESH_OPTIMIZATIONS | text | NO | The refresh optimizations. For example, empty refresh or primary key/foreign key operations applied during the refresh of the materialized view.
NoteThis field is compatible only. The default value is NULL. |
| ADDITIONAL_EXECUTIONS | text | NO | The additional executions. For example, index rebuild or log operations involved during the refresh of the materialized view.
NoteThis field is compatible only. The default value is NULL. |
| START_TIME | datetime | NO | The start time of the refresh. |
| END_TIME | datetime | NO | The end time of the refresh. |
| ELAPSED_TIME | bigint(20) | NO | The duration of the refresh, in seconds. |
| LOG_SETUP_TIME | bigint(1) | NO | The time taken to set up the logs for the materialized view, in seconds
NoteThis field is compatible only. The default value is 0. |
| LOG_PURGE_TIME | bigint(20) | NO | The time taken to purge the logs for the materialized view, in seconds. |
| INITIAL_NUM_ROWS | bigint(20) | NO | The initial number of rows in the materialized view (at the start of the refresh). |
| FINAL_NUM_ROWS | bigint(20) | NO | The final number of rows in the materialized view (at the end of the refresh). |
Sample query
Query the basic timing statistics of a materialized view refresh.
obclient [test_db]> SELECT * FROM oceanbase.CDB_MVREF_STATS;
The query result is as follows:
+-----------+----------+---------------+------------+----------------+-----------------------+-----------------------+---------------------+---------------------+--------------+----------------+----------------+------------------+----------------+
| TENANT_ID | MV_OWNER | MV_NAME | REFRESH_ID | REFRESH_METHOD | REFRESH_OPTIMIZATIONS | ADDITIONAL_EXECUTIONS | START_TIME | END_TIME | ELAPSED_TIME | LOG_SETUP_TIME | LOG_PURGE_TIME | INITIAL_NUM_ROWS | FINAL_NUM_ROWS |
+-----------+----------+---------------+------------+----------------+-----------------------+-----------------------+---------------------+---------------------+--------------+----------------+----------------+------------------+----------------+
| 1002 | test_db | mv2_tbl1_tbl2 | 64672 | COMPLETE | NULL | NULL | 2024-01-17 16:26:28 | 2024-01-17 16:26:28 | 0 | 0 | 0 | 0 | 0 |
| 1002 | test_db | mv1_tbl1_tbl2 | 64670 | COMPLETE | NULL | NULL | 2024-01-17 16:26:28 | 2024-01-17 16:26:28 | 0 | 0 | 0 | 0 | 0 |
| 1004 | SYS | MV1_TEST_TBL1 | 825195 | FAST | NULL | NULL | 2024-01-20 19:16:28 | 2024-01-20 19:16:28 | 0 | 0 | 0 | 0 | 0 |
+-----------+----------+---------------+------------+----------------+-----------------------+-----------------------+---------------------+---------------------+--------------+----------------+----------------+------------------+----------------+
3 rows in set (0.047 sec)