Note
This view is introduced since OceanBase Database V4.3.0.
Purpose
The CDB_MVREF_STMT_STATS view displays information about refresh statements.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| MV_OWNER | varchar(128) | NO | The owner of the materialized view. |
| MV_NAME | varchar(128) | NO | The name of the materialized view. |
| REFRESH_ID | bigint(20) | NO | The ID of the refresh. |
| STEP | bigint(20) | NO | The step ID, which is consecutively numbered starting from 1. |
| SQLID | varchar(32) | YES | The SQL ID. |
| STMT | longtext | NO | The text of the SQL statement. |
| EXECUTION_TIME | bigint(20) | NO | The execution time of the SQL statement, in seconds. |
| EXECUTION_PLAN | longtext | YES | The execution plan. This column is used only by internal engineers for troubleshooting.
NoteThis column is used only for compatibility and is |
Sample query
obclient [test_db]> SELECT * FROM oceanbase.CDB_MVREF_STMT_STATS;
The query result is as follows:
+-----------+----------+---------+------------+------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
| TENANT_ID | MV_OWNER | MV_NAME | REFRESH_ID | STEP | SQLID | STMT | EXECUTION_TIME | EXECUTION_PLAN |
+-----------+----------+---------+------------+------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
| 1002 | SYS | T1_MV1 | 1409121 | 1 | DA98E3A3F4383EC975C94A378A5679A5 | merge into "SYS"."T1_MV1" using (select "DLT_T$$"."C2" AS "C2",sum("DLT_T$$"."DMLFACTOR$$") AS "CNT",sum(("DLT_T$$"."DMLFACTOR$$" * (case when ("DLT_T$$"."C3" is null) then 0 else 1 end))) AS "CNT_C3",sum(("DLT_T$$"."DMLFACTOR$$" * "DLT_T$$"."C3")) AS "SUM_C3" from (select "MLOG$_T1"."OLD_NEW$$" AS "OLD_NEW$$","MLOG$_T1"."SEQUENCE$$" AS "SEQUENCE$$",(case when ("MLOG$_T1"."OLD_NEW$$" = 'N') then 1 else -1 end) AS "DMLFACTOR$$",max("MLOG$_T1"."SEQUENCE$$") over( PARTITION BY "MLOG$_T1"."C1") AS "MAXSEQ$$",min("MLOG$_T1"."SEQUENCE$$") over( PARTITION BY "MLOG$_T1"."C1") AS "MINSEQ$$","MLOG$_T1"."C2" AS "C2","MLOG$_T1"."C3" AS "C3" from "SYS"."MLOG$_T1" where ("MLOG$_T1"."ORA_ROWSCN" > 1706148205539021229) and ("MLOG$_T1"."ORA_ROWSCN" <= 1706148239301769113)) "DLT_T$$" where ((("DLT_T$$"."OLD_NEW$$" = 'N') and ("DLT_T$$"."SEQUENCE$$" = "DLT_T$$"."MAXSEQ$$")) or (("DLT_T$$"."OLD_NEW$$" = 'O') and ("DLT_T$$"."SEQUENCE$$" = "DLT_T$$"."MINSEQ$$"))) group by "DLT_T$$"."C2") "DLT_BASIC_MV$$" on ( ("T1_MV1"."C2" <=> "DLT_BASIC_MV$$"."C2") ) when matched then update set "T1_MV1"."CNT" = ("T1_MV1"."CNT" + "DLT_BASIC_MV$$"."CNT"), "T1_MV1"."CNT_C3" = ("T1_MV1"."CNT_C3" + "DLT_BASIC_MV$$"."CNT_C3"), "T1_MV1"."SUM_C3" = (case when (("T1_MV1"."CNT_C3" + "DLT_BASIC_MV$$"."CNT_C3") = 0) then NULL when ("T1_MV1"."CNT_C3" = 0) then "DLT_BASIC_MV$$"."SUM_C3" when ("DLT_BASIC_MV$$"."CNT_C3" = 0) then "T1_MV1"."SUM_C3" else ("T1_MV1"."SUM_C3" + "DLT_BASIC_MV$$"."SUM_C3") end) delete where ("T1_MV1"."CNT" = 0) when not matched then insert ( C2 , CNT , CNT_C3 , SUM_C3 ) values ( "DLT_BASIC_MV$$"."C2" , "DLT_BASIC_MV$$"."CNT" , "DLT_BASIC_MV$$"."CNT_C3" , (case when ("DLT_BASIC_MV$$"."CNT_C3" = 0) then NULL else "DLT_BASIC_MV$$"."SUM_C3" end) ) where ("DLT_BASIC_MV$$"."CNT" <> 0) | 0 | NULL |
| 1004 | test | t1_mv1 | 1415345 | 1 | 4C4D9DBC6E35BFCBE177C343597C0197 | update `test`.`t1_mv1`,(select `DLT_T$$`.`c2` AS `c2`,sum(`DLT_T$$`.`DMLFACTOR$$`) AS `cnt`,sum((`DLT_T$$`.`DMLFACTOR$$` * (case when (`DLT_T$$`.`c3` is null) then 0 else 1 end))) AS `cnt_c3`,sum((`DLT_T$$`.`DMLFACTOR$$` * `DLT_T$$`.`c3`)) AS `sum_c3` from (select `mlog$_t1`.`OLD_NEW$$` AS `OLD_NEW$$`,`mlog$_t1`.`SEQUENCE$$` AS `SEQUENCE$$`,(case when (`mlog$_t1`.`OLD_NEW$$` = 'N') then 1 else -1 end) AS `DMLFACTOR$$`,max(`mlog$_t1`.`SEQUENCE$$`) over( PARTITION BY `mlog$_t1`.`c1`) AS `MAXSEQ$$`,min(`mlog$_t1`.`SEQUENCE$$`) over( PARTITION BY `mlog$_t1`.`c1`) AS `MINSEQ$$`,`mlog$_t1`.`c2` AS `c2`,`mlog$_t1`.`c3` AS `c3` from `test`.`mlog$_t1` where (`mlog$_t1`.`ORA_ROWSCN` > 1706148016339554735) and (`mlog$_t1`.`ORA_ROWSCN` <= 1706148044936175614)) `DLT_T$$` where (((`DLT_T$$`.`OLD_NEW$$` = 'N') and (`DLT_T$$`.`SEQUENCE$$` = `DLT_T$$`.`MAXSEQ$$`)) or ((`DLT_T$$`.`OLD_NEW$$` = 'O') and (`DLT_T$$`.`SEQUENCE$$` = `DLT_T$$`.`MINSEQ$$`))) group by `DLT_T$$`.`c2`) `DLT_BASIC_MV$$` set `t1_mv1`.`cnt` = (`t1_mv1`.`cnt` + `DLT_BASIC_MV$$`.`cnt`),`t1_mv1`.`cnt_c3` = (`t1_mv1`.`cnt_c3` + `DLT_BASIC_MV$$`.`cnt_c3`),`t1_mv1`.`sum_c3` = (case when ((`t1_mv1`.`cnt_c3` + `DLT_BASIC_MV$$`.`cnt_c3`) = 0) then NULL when (`t1_mv1`.`cnt_c3` = 0) then `DLT_BASIC_MV$$`.`sum_c3` when (`DLT_BASIC_MV$$`.`cnt_c3` = 0) then `t1_mv1`.`sum_c3` else (`t1_mv1`.`sum_c3` + `DLT_BASIC_MV$$`.`sum_c3`) end) where (`t1_mv1`.`c2` <=> `DLT_BASIC_MV$$`.`c2`) | 0 | NULL |
| 1004 | test | t1_mv1 | 1415345 | 2 | 3D9186675C23F505AE54F4C187815E81 | insert into `test`.`t1_mv1`(`c2`,`cnt`,`cnt_c3`,`sum_c3`) select `DLT_BASIC_MV$$`.`c2` AS `c2`,`DLT_BASIC_MV$$`.`cnt` AS `cnt`,`DLT_BASIC_MV$$`.`cnt_c3` AS `cnt_c3`,(case when (`DLT_BASIC_MV$$`.`cnt_c3` = 0) then NULL else `DLT_BASIC_MV$$`.`sum_c3` end) AS `sum_c3` from (select `DLT_T$$`.`c2` AS `c2`,sum(`DLT_T$$`.`DMLFACTOR$$`) AS `cnt`,sum((`DLT_T$$`.`DMLFACTOR$$` * (case when (`DLT_T$$`.`c3` is null) then 0 else 1 end))) AS `cnt_c3`,sum((`DLT_T$$`.`DMLFACTOR$$` * `DLT_T$$`.`c3`)) AS `sum_c3` from (select `mlog$_t1`.`OLD_NEW$$` AS `OLD_NEW$$`,`mlog$_t1`.`SEQUENCE$$` AS `SEQUENCE$$`,(case when (`mlog$_t1`.`OLD_NEW$$` = 'N') then 1 else -1 end) AS `DMLFACTOR$$`,max(`mlog$_t1`.`SEQUENCE$$`) over( PARTITION BY `mlog$_t1`.`c1`) AS `MAXSEQ$$`,min(`mlog$_t1`.`SEQUENCE$$`) over( PARTITION BY `mlog$_t1`.`c1`) AS `MINSEQ$$`,`mlog$_t1`.`c2` AS `c2`,`mlog$_t1`.`c3` AS `c3` from `test`.`mlog$_t1` where (`mlog$_t1`.`ORA_ROWSCN` > 1706148016339554735) and (`mlog$_t1`.`ORA_ROWSCN` <= 1706148044936175614)) `DLT_T$$` where (((`DLT_T$$`.`OLD_NEW$$` = 'N') and (`DLT_T$$`.`SEQUENCE$$` = `DLT_T$$`.`MAXSEQ$$`)) or ((`DLT_T$$`.`OLD_NEW$$` = 'O') and (`DLT_T$$`.`SEQUENCE$$` = `DLT_T$$`.`MINSEQ$$`))) group by `DLT_T$$`.`c2`) `DLT_BASIC_MV$$` where not exists((select 1 from `test`.`t1_mv1` where (`DLT_BASIC_MV$$`.`c2` <=> `t1_mv1`.`c2`))) | 0 | NULL |
| 1004 | test | t1_mv1 | 1415345 | 3 | 189CD424B8FA4E9A0C1620D59ED2407A | delete from `test`.`t1_mv1` where (`t1_mv1`.`cnt` = 0) | 0 | NULL |
+-----------+----------+---------+------------+------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
4 rows in set (0.06 sec)