Note
This view is available starting with V4.3.0.
Purpose
The CDB_MVREF_CHANGE_STATS view displays statistics related to materialized view refreshes. These statistics show how many data changes occurred in the base tables associated with each materialized view during a refresh, as well as the information required to load these changes.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| TBL_OWNER | varchar(128) | NO | The name of the base table owner. |
| TBL_NAME | varchar(128) | NO | The name of the base table. |
| MV_OWNER | varchar(128) | NO | The name of the materialized view owner. |
| MV_NAME | varchar(128) | NO | The name of the materialized view. |
| REFRESH_ID | bigint(20) | NO | The refresh ID. |
| NUM_ROWS_INS | bigint(20) | YES | The number of rows inserted. |
| NUM_ROWS_UPD | bigint(20) | YES | The number of rows updated. |
| NUM_ROWS_DEL | bigint(20) | YES | The number of rows deleted. |
| NUM_ROWS_DL_INS | bigint(1) | NO | The number of rows inserted by direct load.
NoteThis column is compatible only and its value is 0 by default. |
| PMOPS_OCCURRED | varchar(1) | NO | Indicates whether a partition maintenance operation (PMOP) occurred.
NoteThis column is compatible only and its value is N by default. |
| PMOP_DETAILS | text | NO | The details of the PMOP in the following format
NoteThis column is compatible only and its value is NULL by default. |
| NUM_ROWS | bigint(20) | YES | The number of rows in the base table at the start of the refresh operation. |
Sample query
Query the statistics related to materialized view refreshes.
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)