创建物化视图commit_oracle 中物化视图讲解

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;

可以直接创建成功