范范｀blog

一、oracle物化視圖基本概念

 物化視圖首先需要創建物化視圖日志,

 oracle依據用戶創建的物化視圖日志來創建物化視圖日志表，

 物化視圖日志表的名稱為mlog$_后面跟基表的名稱，

 如果表名的長度超過20位，則只取前20位，當截短后出現名稱重復時，oracle會自動在物化視圖日志名稱后面加上數字作為序號。

 創建物化視圖日志在建立時有多種選項：可以指定為rowid、primary key和object id幾種類型，同時還可以指定sequence或明確指定列名。

 上面這些情況產生的物化視圖日志的結構都不相同。

 任何物化視圖都會包括的列：

  snaptime$$：用于表示刷新時間。

  dmltype$$：用于表示dml操作類型，i表示insert，d表示delete，u表示update。

  old_new$$：用于表示這個值是新值還是舊值。n（ew）表示新值，o（ld）表示舊值，u表示update操作。

  change_vector$$表示修改矢量，用來表示被修改的是哪個或哪幾個字段。

 如果with后面跟了primary key，則物化視圖日志中會包含主鍵列。

 如果with后面跟了rowid，則物化視圖日志中會包含： m_row$$：用來存儲發生變化的記錄的rowid。

 如果with后面跟了object id，則物化視圖日志中會包含：sys_nc_oid$：用來記錄每個變化對象的對象id。

 如果with后面跟了sequence，則物化視圖日子中會包含：sequence$$：給每個操作一個sequence號，從而保證刷新時按照順序進行刷新。

 如果with后面跟了一個或多個column名稱，則物化視圖日志中會包含這些列。

二、oracle物化視圖日志

 1.primary key

  drop table test_id;    --刪除表

  create table test_id(id number,name varchar2(30),mark number); --創建表

  alter table test_id add constraint pk_test_id primary key (id);  --增加主鍵

  drop materialized view log on test_id;--刪除物化視圖日志

  create materialized view log on test_id tablespace ttts with primary key; --依據主鍵創建物化視圖日志

  --系統針對日志建表

  sql> desc mlog$_test_id;

  name            type        nullable default comments

  --------------- ----------- -------- ------- --------

  id              number      y        用主鍵記錄發生dml操作的行

  snaptime$$      date        y        snaptime$$列記錄了刷新操作的時間。                

  dmltype$$       varchar2(1) y        dmltype$$的記錄值i、u和d，表示操作是insert、update還是delete。                

  old_new$$       varchar2(1) y        old_new$$表示物化視圖日志中保存的信息是dml操作之前的值（舊值）還是dml操作之后的值（新值）。除了o和n這兩種類型外，對于update操作，還可能表示為u。                

  change_vector$$ raw(255)    y        change_vector$$記錄dml操作發生在那個或那幾個字段上

  --當創建物化視圖日志使用primary key時，oracle創建臨時表 RUPD$_基礎表

  sql> desc rupd$_test_id;

  name            type        nullable default comments

  --------------- ----------- -------- ------- --------

  id              number      y                        

  dmltype$$       varchar2(1) y                        

  snapid          integer     y                        

  change_vector$$ raw(255)    y  

 2.rowid

  drop table test_rowid;    --刪除表

  create table test_rowid(id number,name varchar2(30),mark number); --創建表

  drop materrialized view log on test_rowid;

  --create materialized view log on test_rowid with rowid, sequence (id, name) including new values ;

  create materialized view log on test_rowid with rowid;--依據rowid創建物化視圖日志

  sql> desc mlog$_test_rowid;

  name            type          nullable default comments

  --------------- ------------- -------- ------- --------

  m_row$$         varchar2(255) y                        

  snaptime$$      date          y                        

  dmltype$$       varchar2(1)   y                        

  old_new$$       varchar2(1)   y                        

  change_vector$$ raw(255)      y

 3.object id

  create type test_object as object (id number, name varchar2(30), num number);--創建類型

  create table test_objid of test_object; --創建表

  create materialized view log on test_objid with object id;--依據object id創建物化視圖日志

  sql> desc mlog$_test_objid;

  name            type        nullable default comments

  --------------- ----------- -------- ------- --------

  sys_nc_oid$     raw(16)     y                        

  snaptime$$      date        y                        

  dmltype$$       varchar2(1) y                        

  old_new$$       varchar2(1) y                        

  change_vector$$ raw(255)    y 

 4.sequence+rowid+(屬性列)

  drop table test_sq;    --刪除表

  create table test_sq(id number,name varchar2(30),mark number); --創建表

  drop materialized view log on test_sq;--刪除物化視圖日志

  create materialized view log on test_sq tablespace ttts with sequence; --依據sequence創建物化視圖日志

  --ora-12014: 表 'test_sq' 不包含主鍵約束條件

  create materialized view log on test_sq with sequence (id, name,num) including new values;--包含基礎表的所有列

  --ora-12014: 表 'test_sq' 不包含主鍵約束條件

  alter table test_sq add constraint uk_test_sq unique (id,name);  --增加uk

  create materialized view log on test_sq with sequence (id,name) including new values;

  --ora-12014: 表 'test_sq' 不包含主鍵約束條件

  即主鍵、rowid或object id用來唯一表示物化視圖日志中的記錄，sequence不能唯一標識記錄，故不能單獨用來建日志。

  create materialized view log on test_sq with rowid,sequence (id, name) including new values ;

  sql> desc mlog$_test_sq;

  name            type          nullable default comments

  --------------- ------------- -------- ------- --------

  id              number        y    建立物化視圖時指明的列會在物化視圖日志中進行記錄。                     

  name            varchar2(30)  y                        

  m_row$$         varchar2(255) y                        

  sequence$$      number        y    sequence會根據操作發生的順序對物化視圖日志中的記錄編號。                    

  snaptime$$      date          y                        

  dmltype$$       varchar2(1)   y                        

  old_new$$       varchar2(1)   y                        

  change_vector$$ raw(255)      y 

三、oracle物化視圖日志表

 基礎表：test_id，test_rowid，test_objid，test_sq

 日志表：mlog$_test_id，mlog$_test_rowid，mlog$_test_objid，mlog$_test_sq

 1.新增

  insert into test_id    values (1, 'a', 5);

  insert into test_rowid values (1, 'a', 5);

  insert into test_objid values (1, 'a', 5);

  insert into test_sq    values (1, 'a', 5);

  commit;

 2.修改

  update test_id    set name = 'c' where id = 1;

  update test_rowid set name = 'c' where id = 1;

  update test_objid set name = 'c' where id = 1;

  update test_sq    set name = 'c' where id = 1;

  commit;

 3.刪除

  delete test_id   ;

  delete test_rowid;

  delete test_objid;

  delete test_sq   ;

  commit;

 在每一步commit后查看日志表記錄。

四、oracle物化視圖日志表字段取值解析

 1.snaptime$$

  當基本表發生dml操作時，會記錄到物化視圖日志中，這時指定的時間4000年1月1日0時0分0秒（物化視圖未被刷新）。

  如果物化視圖日志供多個物化視圖使用，則一個物化視圖刷新后會將它刷新的記錄的時間更新為它刷新的時間。

  只有建立快速刷新的物化視圖才能使用物化視圖日志，如果只建立一個物化視圖，則物化視圖刷新完會將物化視圖日志清除掉

 2.dmltype$$

  操作類型比較簡單：只包括i(insert)、d(delete)和u(update)三種。

 3.old_new$$

  新舊值也包括三種：o表示舊值（一般對應的操作時delete）、n表示新值（一般對應的操作是insert），還有一種u（對應update操作）。 

  需要注意，對于基于主鍵的物化視圖日志，如果更新了主鍵，則update操作轉化為一條delete操作，一條insert操作。最后是delete操作。 

  唯一的區別是每條update操作都對應物化視圖日志中的兩條記錄。

  一條對應update操作的原記錄dmltype$$和old_new$$都為u，一條對應update操作后的新記錄，dmltype$$為u，old_new$$為n。

  當建立物化視圖日志時指出了including new values語句時，就會出現這種情況。 

 4.change_vector$$

  最后簡單討論一下change_vector$$列。

  insert和delete操作都是記錄集的，即insert和delete會影響整條記錄。

  而update操作是字段集的，update操作可能會更新整條記錄的所有字段，也可能只更新個別字段。

  無論從性能上考慮還是從數據的一致性上考慮，物化視圖刷新時都應該是基于字段集。

  oracle就是通過change_vector$$列來記錄每條記錄發生變化的字段包括哪些。

  基于主鍵、rowid和object id的物化視圖日志在change_vector$$上略有不同，但是總體設計的思路是一致的。

  change_vector$$列是raw類型，其實oracle采用的方式就是用每個bit位去映射一個列。

  比如：第一列被更新設置為02，即00000010。

  第二列設置為04，即00000100，

  第三列設置為08，即00001000。

  當第一列和第二列同時被更新，則設置為06，00000110。

  如果三列都被更新，設置為0e，00001110。

  依此類推，第4列被更新時為0x10，第5列0x20，第6列0x40，第7列0x80，第8列0x100。

  當第1000列被更新時，change_vector$$的長度為1000/4+2為252。

  除了可以表示update的字段，還可以表示insert和delete。delete操作change_vector$$列為全0，具體個數由基表的列數決定。

  insert操作的最低位為fe，如果基表列數較多，而存在高位的話，所有的高位都為ff。

  如果insert操作是前面討論過的由update操作更新了主鍵造成的，則這個insert操作對應的change_vector$$列為全ff。

  可以看到，正如上面分析的，insert為fe，delete為00，對第一列的更新為02，第二列為04，第二列和第三列都更新為0c。需要注意，正常情況下，第一列會從02開始。

  但是如果對mlog$表執行了truncate操作，或者重建了物化視圖日志，則可能造成第一列開始位置發生偏移。

  這個結果和rowid類型基本一致，不同的是，如果更新了主鍵，會將update操作在物化視圖日志中記錄為一條delete和一條insert，不過這時insert對應的change_vector$$的值是ff。

  這個結果也和rowid類型基本一致，需要注意的是，由于對象表包含兩個隱含列，因此id不再是第一個字段，而是第三個，因此對應的值是08。

  最后看一個包含列數較多的例子，唯一需要注意的是，低位在左，高位在右。

五、oracle物化視圖

 1.物化視圖mv_test_id

  create materialized view mv_test_id refresh fast on commit as

    select * from test_id;                                           --commit時物化視圖被刷新

 2.物化視圖mv_test_rowid

  create materialized view mv_test_rowid refresh fast as

    select name, count(*) from test_rowid group by name;

    --ORA-32401: "TT"."TEST_ROWID" 上的實體化視圖日志沒有新值

    alter materialized view log on test_rowid add including new values;

  create materialized view mv_test_rowid refresh fast as

    select name, count(*) from test_rowid group by name;

    --ORA-12033: 不能使用 "TT"."TEST_ROWID" 上實體化視圖日志中的過濾器列

    alter materialized view log on test_rowid add (name); 

  create materialized view mv_test_rowid refresh fast as

    select name, count(*) from test_rowid group by name;

 3.物化視圖mv_test_objid

  create materialized view mv_test_objid refresh fast as

    select * from test_objid;

    --ORA-12014: 表 'TEST_OBJID' 不包含主鍵約束條件

  alter table test_objid add constraint pk_test_objid primary key (id);  --增加主鍵 

  create materialized view mv_test_objid refresh fast as

    select * from test_objid;

    --ORA-23415: "TT"."TEST_OBJID" 的實體化視圖日志不記錄主鍵   

  alter materialized view log on test_objid add (id); 

  alter materialized view log on test_objid add primary key (id); 

  drop materialized view  log on test_objid;

  create materialized view log on test_objid tablespace ttts with primary key including new values;

  create materialized view mv_test_objid refresh fast as

    select * from test_objid;   

 4.物化視圖mv_test_sq

  create materialized view mv_test_sq refresh fast as

    select name, count(*) from test_sq group by name;                --需要用exec dbms_mview.refresh('mv_test_sq')來刷新

 5.物化視圖刷新

  exec dbms_mview.refresh('mv_test_rowid');

  exec dbms_mview.refresh('mv_test_objid');

  exec dbms_mview.refresh('mv_test_sq');

 物化視圖刷新后日志表記錄被清空。

  refresh fast as             調用exec dbms_mview.refresh('mv_基本表')時物化視圖刷新

  refresh fast on commit as   在commit時物化視圖刷新

  refresh fast on demand      定時物化視圖刷新

   create materialized view mv_test_sq2 refresh fast on demand

   with rowid start with to_date('22-04-2011 16:30:01', 'dd-mm-yyyy hh24:mi:ss') next /*1:hrs*/ sysdate + 1/(24*60)

   as select id,count(*) from test_sq group by id;

六、錯誤提示：

 --ORA-32401: "TT"."TEST_ROWID" 上的實體化視圖日志沒有新值

 alter materialized view log on test_rowid add including new values;

 --ORA-12033: 不能使用 "TT"."TEST_ROWID" 上實體化視圖日志中的過濾器列

 alter materialized view log on test_rowid add (name); 

 --ORA-12014: 表 'TEST_OBJID' 不包含主鍵約束條件

 alter table test_objid add constraint pk_test_objid primary key (id);  --增加主鍵   

 --ORA-23415: "TT"."TEST_OBJID" 的實體化視圖日志不記錄主鍵

 drop materialized view  log on test_objid;

 create materialized view log on test_objid tablespace ttts with primary key including new values;

七、相關語法：

 create {materialized view | snapshot} log on <tab> [tablespace <ts>] [storage (…)] [pctfree <10>] [pctused <40>] [initrans <1>] [maxtrans <n>] [logging | nologging] [cache | nocache] [noparallel | parallel [<n>]] [partition…] [lob…] [using index…] [with [primary key] [, rowid] [(<col> [, …])] ] [{including | excluding} new values];

 alter {materialized view | snapshot} log on <tab> [add [primary key] [, rowid] [(<col> [, …])] ] […];

 drop {materialized view | snapshot} log on <tab>;

 create {materialized view | snapshot} <mview>[tablespace <ts>] [storage (…)] [pctfree <10>] [pctused <40>] [initrans <1>] [maxtrans <n>] [logging | nologging] [cache | nocache] [noparallel | parallel [<n>]] [cluster <clust> (<col> [, …])] [lob…] [partition…] [build {immediate | deferred}] [on prebuilt table [{with | without} reduced precision]] [using index…] [ refresh [fast | complete | force] [on commit | on demand] [start with ‘<date>’] [next ‘<date>’] [with {primary key | rowid}] [using [default] [master | local] rollback segment [<rbs>]] ] | never refresh ] [for update] [{enable | disable} query rewrite] as <query>;

 alter {materialized view | snapshot} <mview> … [compile];

 drop {materialized view | snapshot} <mview>;

八、舉例

 connect pubr/bit@datasource ;

 drop materialized view log on pubr.allactive;  --刪除物化視圖日志

 create materialized view log

     on pubr.allactive tablespace logts with primary key; --創建物化視圖日志

 connect ttowb/bit;

 drop materialized view allactive_tt;        --刪除物化視圖

 create materialized view allactive_tt

 refresh fast

 as select ID,CATEGORY,FLOWID,MASTATUS,BASTATUS,APPLYDATETIME,CREATEDATETIME,COMMITDATETIME,BITSPNO,ARCHIVETIME,

 DESCRIPTION,OPERTYPE,ISVALID,INVALIDREASON,INVALIDDATETIME,INVALIDPNO,ACTIVETABLENAME,PARENTID,STANID,REALTYPEID,

 CORRECTID,to_date('1900-01-01') allactive_rtime from pubr.allactive@pubrowb;  --創建物化視圖

九、參考

 http://tech.ddvip.com/2008-09/122180687967473.html  Oracle物化視圖：創建最簡單物化視圖

 http://yangtingkun.itpub.net/post/468/20584         Oracle如何根據物化視圖日志快速刷新物化視圖

 http://blog.itpub.net/post/468/20498                物化視圖日志結構