Description
The CDB_MVREF_CHANGE_STATS view displays statistics related to materialized view refreshes. These statistics show how much data changes occur on the base tables associated with each materialized view refresh, and the amount of information required to load these changed data.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | Tenant ID |
| TBL_OWNER | varchar(128) | NO | Base Table Owner Name |
| TBL_NAME | varchar(128) | NO | Base Table Name |
| MV_OWNER | varchar(128) | NO | Materialized View Owner |
| MV_NAME | varchar(128) | NO | Materialized View Name |
| REFRESH_ID | bigint(20) | NO | Refresh ID |
| RETRY_ID | bigint(20) | NO | The ID of the retry batch to which this change record belongs. |
| NUM_ROWS_INS | bigint(20) | YES | Number of inserted rows |
| NUM_ROWS_UPD | bigint(20) | YES | Number of updated rows |
| NUM_ROWS_DEL | bigint(20) | YES | Number of rows deleted |
| NUM_ROWS_DL_INS | bigint(1) | NO | direct load insert rows
NoteThis field is only compatible and its default value is 0. |
| PMOPS_OCCURRED | varchar(1) | NO | Indicates whether a partition maintenance operation (PMOP) has occurred.
NoteThis field is only compatible with the value N by default. |
| PMOP_DETAILS | text | NO | The details of PMOP are represented in the following format:
NoteThis field is only compatible and its default value is NULL. |
| NUM_ROWS | bigint(20) | YES | Number of rows in the base table at the start of the refresh operation |
Sample query
Query statistics related to materialized view refresh.
obclient [test_db]> SELECT * FROM oceanbase.CDB_MVREF_CHANGE_STATS;
The query result is as follows:
+-----------+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
| TENANT_ID | TBL_OWNER | TBL_NAME | MV_OWNER | MV_NAME | REFRESH_ID | NUM_ROWS_INS | NUM_ROWS_UPD | NUM_ROWS_DEL | NUM_ROWS_DL_INS | PMOPS_OCCURRED | PMOP_DETAILS | NUM_ROWS |
+-----------+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
| 1002 | test_db | test_tbl2 | test_db | mv2_tbl1_tbl2 | 64672 | 0 | 0 | 0 | 0 | N | NULL | 0 |
| 1002 | test_db | test_tbl2 | test_db | mv1_tbl1_tbl2 | 64670 | 0 | 0 | 0 | 0 | N | NULL | 0 |
| 1002 | test_db | test_tbl1 | test_db | mv2_tbl1_tbl2 | 64672 | 0 | 0 | 0 | 0 | N | NULL | 0 |
| 1002 | test_db | test_tbl1 | test_db | mv1_tbl1_tbl2 | 64670 | 0 | 0 | 0 | 0 | N | NULL | 0 |
| 1004 | SYS | TEST_TBL1 | SYS | MV1_TEST_TBL1 | 825195 | 3 | 0 | 0 | 0 | N | NULL | 0 |
+-----------+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
5 rows in set (0.082 sec)