oracle 中物化视图讲解
oracle中的物化视图首先需要创建物化视图日志,oracle根据用户创建的物化视图日志来创建物化视图日志表,物化视图日志表名称为mlog$_基表名,如果表名超过20位,则只取前20位,当截断后名字有重复则加上序列号,以便区分。
物化视图日志表介绍
物化视图日志表创建选项:
创建物化视图有多种选项类型,分别为 rowid ,primary key和object id几种类型,同时还可以指定sequence 或明确指定列名,同时上面的几种情况产生的物化视图结构也各不相同。
物化视图日志表公共的列
物化视图无论哪种方式创建,都有几个公共列,分别如下
snaptime$$:用于表示刷新时间。当基本表发生dml操作时,会记录到物化视图中,这个时间是4000年1月1 日0时0分(表示物化视图未被刷新),如果物化视图日志供多个物化视图使用,则第一个物化视图刷新后它将刷新该记录的时间为更新为刷新时间,只有建立快速刷新的物化视图才能使用物化日志,如果只有一个物化视图使用它,则物化视图刷新完后会将物化日志清除掉
dmltype$$:用于表示dml操作类型,i表示insert,d表示delete,u表示update。
old_new$$:用于表示这个值是新值还是旧值。n(ew)表示新值,o(ld)表示旧值,u表示update操作。
change_vector$$:表示修改矢量,用来表示被修改的是哪个或哪几个字段
物化视图日志表特殊列
如果使用with parimary 则物化日志中包含主键字段
如果使用with rowid 则物化日志中包含m_row$$ 表示发生的rowid
如过使用with object id ,则物化视图日中中会包含 sys_nc_oid$ 用来记录每个变化对象的对象id
如果使用了sequence 则物化视图中会包含sequence$$,给每个操作分配一个sequence,保证刷新时按照顺序进行刷新
如果with后面跟了一个或多个column名,则物化视图中就会包含这些列
创建物化视图日志表
1.primary key
drop table table_pk;
CREATE TABLE table_pk
(
id number(10) NOT NULL
, stname VARCHAR2(20)
, address VARCHAR2(200)
);
alter table table_pk add constraint table_pk_id primary key (id); --增加主键
drop materialized view log on table_pk;
create materialized view log on table_pk with primary key ;
desc mlog$_table_pk;
使用primary_key 时,oracle还会创建一个临时表 rupd$_基础表
2. rowid
drop table table_rowid;
CREATE TABLE table_rowid
(
id number(10) NOT NULL primary key
, stname VARCHAR2(20)
, address VARCHAR2(200)
);
drop materialized view log on table_rowid;
create materialized view log on table_rowid with ROWID;
desc mlog$_table_rowid;
3. object_id
创建类型
drop type table_object_type;
create type table_object_type as object ( id number(10), stname VARCHAR2(20) , address VARCHAR2(200) );--创建类型
drop table table_object;
CREATE TABLE table_object of table_object_type;
drop materialized view log on table_object;
create materialized view log on table_object with object id;
desc mlog$_table_object;
4. sequence
drop table table_sq;
CREATE TABLE table_sq
(
id number(10) NOT NULL
, stname VARCHAR2(20)
, address VARCHAR2(200)
);
drop materialized view log on table_sq;
create materialized view log on table_sq with sequence;
直接用with sequence 会创建失败,错误信息 表 'TABLE_SQ' 不包含主键约束条件
create materialized view log on table_sq with sequence(id,stname,address) including new values;
with sequence 就算包含字段也会创建失败,错误信息 表 'TABLE_SQ' 不包含主键约束条件
从上面看出 主键 、rowid、 object id都是可以唯一标识行数据的,所有只用使用sequence 是不能单独使用来创建日志。
可以使用 主键+sequence 和 rowid+sequence
5. 主键+sequence
alter table table_sq add constraint table_sq_id primary key (id); --增加主键
drop materialized view log on table_sq;
create materialized view log on table_sq with primary key, sequence;
desc mlog$_table_sq;
drop materialized view log on table_sq;
将表的全部字段都记录进去,这里sequence 一定不要把主键字段名在写入了,写入会报错
create materialized view log on table_sq with primary key, sequence(stname,address) including new values;
desc mlog$_table_sq;
6.rowid+sequence
drop table table_sq;
CREATE TABLE table_sq
(
id number(10) NOT NULL
, stname VARCHAR2(20)
, address VARCHAR2(200)
);
drop materialized view log on table_sq;
create materialized view log on table_sq with rowid,sequence;
desc mlog$_table_sq;
全部数据都加入日志表
drop materialized view log on table_sq;
create materialized view log on table_sq with rowid,sequence(id,stname,address) including new values ;
desc mlog$_table_sq;
操作基本表查看日志表
基础表:table_pk,table_rowid,table_object,table_sq(rowid+sequence)
日志表:mlog$_table_pk,mlog$_table_rowid,mlog$_table_object,mlog$_table_sq
1.新增
insert into table_pk values (1, 'a', '1a');
insert into table_rowid values (1, 'a', '1a');
insert into table_object values (1, 'a', '1a');
insert into table_sq values (1, 'a', '1a');
commit;
2.修改
update table_pk set stname = 'c' , address='cu' where id = 1;
update table_rowid set stname = 'c' , address='cu' where id = 1;
update table_object set stname = 'c' , address='cu' where id = 1;
update table_sq set stname = 'c' , address='cu' where id = 1;
commit;
3.删除
delete table_pk ;
delete table_rowid;
delete table_object;
delete table_sq ;
commit;
select *from mlog$_table_pk;
select *from mlog$_table_rowid;
select *from mlog$_table_object;
select *from mlog$_table_sq ;
物化视图
create materialized view [view_name]
build [immediate|deferred]
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)
]
[enable | disable] query rewrite
as
{创建物化视图用的查询语句}
build [immediate|deferred]: 创建方式 默认immediate 立即产生数据 deferred根据需要产生数据
refresh [fast|complete|force]:刷新方式 fast:增量刷新 complete:全部刷新 forc:这个是默认 刷新方式,当数据可以使用fast时候就使用fast,否则使用complete模式
on [commit|demand] :视图数据刷新时间 commit 事务提交时刷新 demand在用户需要刷新时候刷新,这就要求用户自己动手去刷新,或者定时job
start with (start_time) next (next_time):从指定时间开始每隔一段时间(由next指定)就刷新一次
[enable | disable] query rewrite: 查询重写,当对物化视图的基表进行查询时候,oracle通过判断分析是否能通过物化视图来得到结果,如果可以则避免重新聚集 或其他操作,默认是disable的
使用主键primary key 的物化日志表,只能创建单表查询的物化视图
create materialized view mv_table_pk refresh fast on commit as
select * from table_pk;
select *from mv_table_pk;
如果我们将单表查询修改成汇总类的如下
create materialized view mv_table_pk refresh fast as
select stname, count(*) from table_pk group by stname;
则报错误如下 RA-12032: 不能使用 "GOODHOPE"."TABLE_PK" 上实体化视图日志中的 rowid 列
我们需要使用rowid的日志方式才能使用group 汇总类的物化视图,我们使用table_rowid来试下
使用rowid物化日志来创建带有汇总的物化视图
drop materialized view mv_table_rowid;
create materialized view mv_table_rowid refresh fast as
select stname, count(*) from table_rowid group by stname;
错误报告 -ORA-32401: "GOODHOPE"."TABLE_ROWID" 上的实体化视图日志没有新值
alter materialized view log on table_rowid add including new values;
create materialized view mv_table_rowid refresh fast as
select stname, count(*) from table_rowid group by stname;
还是报错,报错信息如下
ORA-12033: 不能使用 "GOODHOPE"."TABLE_ROWID" 上实体化视图日志中的过滤器列,我们需要添加列
alter materialized view log on table_rowid add(stname)
create materialized view mv_table_rowid refresh fast as
select stname, count(*) from table_rowid group by stname;
现在可以成功的创建物化视图了
rowid+sequence 模式日志也可以创建聚合类物化视图
create materialized view mv_table_sq refresh fast as
select stname, count(*) from table_sq group by stname;
可以直接创建成功