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 associated base tables during each refresh and 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 directly loaded.
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 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)