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;
可以直接建立成功