物化视图与物化视图日志

【摘要】

            今天巡检数据库发现存在一张220G的数据库实体表，通过命名(MLOG$_TABLE_NAME)可以看出这是一张物化视图日志表，基表只有120M，正常情况下物化视图日志会在物化视图刷新后被数据库自动清理，这里的刷新无视刷新规则，fast、complete和force都会导致日志被清空。

【正文】

            基表，物化视图日志和物化视图关系

            一般情况下，创建物化视图会直接从基表取数据，如果数据量比较大每次都全量获取数据会对数据库造成一定压力，为了能够增量获得基表的变化，可以考虑创建物化视图日志，物化视图日志根据基表主键产生记录，每次基表发生DML操作都会产生一条或多条记录，物化视图可以根据这些记录做到增量刷新，刷新完成后Oracle会自动清空物化视图日志。

            经过实验，发现以下注意点

1. 创建物化视图日志必须要求基表存在主键
2. 创建物化视图会自动刷新并清空物化视图日志
3. user_registered_mviews视图中的can_use_log默认为yes，即使改为no也可以使用并清空物化视图日志
4. 如果修改基表定义导致物化视图状态为INVALID，但是又改回原定义，此时的物化视图在下次执行后会自动改为VALID状态
5. 如果物化视图日志因其他原因导致很大，需要手动清理，可以通过Oracle自带存储过程（purge_mview_from_log）进行处理

具体语法：

exec dbms_mview.purge_mview_from_log(21);

--21为mviews_id，从dba_base_table_mviews得到

物化视图未及时更新，物化视图日志又被手动清理后，再次刷新物化视图会导致报错，此时需要重建物化视图

ORA-12034: materialized view log on "owner"."table_name" younger than last refresh

            以下为实验步骤

create table t_mv (id int,name varchar2(20),age int);--创建测试表

alter table t_mv add constraint  pk_id primary key(id);--添加主键

create materialized view log on t_mv;--创建物化视图日志

select * from MLOG$_T_MV; --空

insert into t_mv values (1,'lpp',26);

commit;

select * from t_mv;--1条数据

select * from MLOG$_T_MV;--1条数据

select * from user_registered_mviews; --空

create materialized view mv_t_mv refresh fast as select id,name from t_mv;

select * from mv_t_mv;--1条数据；

select * from MLOG$_T_MV; --空, 创建时就会清空日志表

insert into t_mv values (2,'lppe',26);

commit;

select * from t_mv;--2条数据

select * from MLOG$_T_MV;--1条数据，

select * from user_registered_mviews; --1条数据，can_use_log字段默认是yes

execute dbms_mview.refresh('mv_t_mv');--命令行执行

select * from mv_t_mv;--2条数据

select * from MLOG$_T_MV;--空

update user_registered_mviews set can_use_log='NO';--普通用户无权限，sysdba提示不能修改虚拟列

select * from user_registered_mviews; --1条数据，can_use_log字段默认是no,通过修改基表SYS.REG_SNAP$

insert into t_mv values (3,'lppf',26);

commit;

select * from t_mv;--3条数据

select * from MLOG$_T_MV;--1条数据

execute dbms_mview.refresh('mv_t_mv');--命令行执行

select * from mv_t_mv;--3条数据

select * from MLOG$_T_MV;--空，can_use_log字段是no依然可以读取并清空物化视图日志

alter table t_mv drop column name; --修改表定义,删除name字段

select * from all_objects where object_name='MV_T_MV';--物化视图状态失效

alter table t_mv add name varchar2(20);--修改表定义，添加name字段

select * from all_objects where object_name='MV_T_MV';--物化视图状态依然失效

select * from dba_mviews;--上次刷新时间 2020/1/5 6:38:26，staleness 字段为NEEDS_COMPILE，服务器时间有误

select * from dba_base_table_mviews;--上次刷新时间 2020/1/5 6:38:26,mview_id 21

insert into t_mv values (4,26,'lppg');--修改表定义导致字段顺序调整

commit;

select * from t_mv;--4条数据

select * from MLOG$_T_MV;--1条数据

execute dbms_mview.refresh('mv_t_mv');--命令行执行成功，此时发现物化视图状态为有效

select * from mv_t_mv;--4条数据

select * from MLOG$_T_MV;--空，此时发现物化视图状态为有效

alter table t_mv drop column name; --修改表定义，删除name字段

select * from all_objects where object_name='MV_T_MV';--物化视图状态失效

insert into t_mv values (5,26);--删除了name字段

select * from t_mv;--5条数据

select * from MLOG$_T_MV;--1条数据

execute dbms_mview.refresh('mv_t_mv');--命令行执行报错 ORA-00904: "T_MV"."NAME": invalid identifier

insert into t_mv values (6,26);--删除了name字段

select * from t_mv;--6条数据

select * from MLOG$_T_MV;--2条数据

insert into t_mv values (7,26);--删除了name字段

select * from t_mv;--7条数据

select * from MLOG$_T_MV;--3条数据，不能刷新导致日志一直增大，可能达到上百G

exec dbms_mview.purge_mview_from_log(21);--手动在命令行清理物化视图日志,从mview_id 21开始，21是从dba_base_table_mviews得到

select * from t_mv;--7条数据

select * from MLOG$_T_MV;--已清空

insert into t_mv values (8,26);--删除了name字段

alter table t_mv add name varchar2(20);--修改表定义，添加name字段

execute dbms_mview.refresh('mv_t_mv');--命令行执行报错 ORA-12034: materialized view log on "LPP"."T_MV" younger than last refresh

alter materialized view mv_t_mv compile;--尝试编译物化视图,成功编译

execute dbms_mview.refresh('mv_t_mv');--命令行执行报错 ORA-12034: materialized view log on "LPP"."T_MV" younger than last refresh

drop materialized view mv_t_mv;--删除物化视图

create materialized view mv_t_mv refresh fast as select id,name from t_mv;--重建物化视图

select * from mv_t_mv;--8条数据

select * from MLOG$_T_MV;--空

insert into t_mv values (9,26,'lpp');

select * from t_mv;--9条数据

select * from MLOG$_T_MV;--1条数据

alter materialized view mv_t_mv refresh force on demand;--修改刷新机制

execute dbms_mview.refresh('mv_t_mv');--命令行执行

select * from mv_t_mv;--9条数据

select * from MLOG$_T_MV;--空

drop materialized view mv_t_mv;--删除物化视图

insert into t_mv values (10,26,'lppa');

select * from t_mv;--10条数据

select * from MLOG$_T_MV;--1条数据

create materialized view mv_t_mv refresh force on demand as select id,name from t_mv;--重建物化视图

select * from mv_t_mv;--9条数据

select * from MLOG$_T_MV;--空

drop materialized view mv_t_mv;--删除物化视图

insert into t_mv values (11,26,'lppb');

select * from t_mv;--11条数据

select * from MLOG$_T_MV;--1条数据

create materialized view mv_t_mv refresh complete on demand as select id,name from t_mv;--重建物化视图

select * from mv_t_mv;--11条数据

select * from MLOG$_T_MV;--空

delete from t_mv where id=11; --1条数据

update t_mv set age=25; --11条数据

【总结】

            经常检查数据库无效对象，尤其是物化视图是否失效，及时排查及时处理，避免浪费过多的空间。