Note
This view is available starting with V4.3.0.
Purpose
The DBA_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 its refreshes, as well as the information required to load these changes.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| 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. |
| RETRY_ID | bigint(20) | NO | The retry batch ID to which this change record belongs.
NoteThis field is available starting with V4.4.2 BP2. |
| 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 the default value is 0. |
| PMOPS_OCCURRED | varchar(1) | NO | Indicates whether a partition maintenance operation (PMOP) occurred. Valid values:
NoteThis column is compatible only and the default value is N. |
| PMOP_DETAILS | text | NO | The details of the PMOP. Valid values:
NoteThis column is compatible only and the default value is NULL. |
| 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.DBA_MVREF_CHANGE_STATS;
The query result is as follows:
+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
| 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 |
+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
| test_db | test_tbl1 | test_db | mv1_tbl1_tbl2 | 64670 | 0 | 0 | 0 | 0 | N | NULL | 0 |
| test_db | test_tbl2 | test_db | mv1_tbl1_tbl2 | 64670 | 0 | 0 | 0 | 0 | N | NULL | 0 |
| test_db | test_tbl1 | test_db | mv2_tbl1_tbl2 | 64672 | 0 | 0 | 0 | 0 | N | NULL | 0 |
| test_db | test_tbl2 | test_db | mv2_tbl1_tbl2 | 64672 | 0 | 0 | 0 | 0 | N | NULL | 0 |
+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
4 rows in set (0.029 sec)