Note
This view is available starting with V4.3.0.
Purpose
The DBA_MVREF_CHANGE_STATS view displays statistics related to the refresh of materialized views. These statistics show how many data changes occurred in the base tables associated with each materialized view refresh and 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. |
| 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 value is 0 by default. |
| PMOPS_OCCURRED | varchar(1) | NO | Indicates whether a partition maintenance operation (PMOP) occurred.
NoteThis column is compatible only and the value is N by default. |
| PMOP_DETAILS | text | NO | The details of the PMOP, in the following format:
NoteThis column is compatible only and the 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 the refresh of materialized views.
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)