物化视图

1. 基于rowid的物化视图

1.1 创建基表

CREATE TABLE B (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); 
CREATE TABLE c (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); 
CREATE TABLE A (ID NUMBER, BID NUMBER, CID NUMBER, NUM NUMBER,   
  CONSTRAINT FK_A_B_BID FOREIGN KEY (BID) REFERENCES B(ID),   
  CONSTRAINT FK_A_C_BID FOREIGN KEY (CID) REFERENCES C(ID));

1.2 创建基表log

      CREATE MATERIALIZED VIEW LOG ON A WITH ROWID;
   CREATE MATERIALIZED VIEW LOG ON b WITH ROWID;
    CREATE MATERIALIZED VIEW LOG ON c WITH ROWID;

1.3,创建物化视图,注意rowid必需包含

    CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST ON COMMIT AS
    SELECT C.ID CID, C.NAME CNAME, B.ID BID, B.NAME BNAME, A.NUM,
    A.ROWID AROWID, B.ROWID BROWID, C.ROWID CROWID,'a'
    FROM A, B, C WHERE A.BID = B.ID AND A.CID = C.ID

1.4 使用union all,SELECT列表中必须包含一列维护列，叫做UNION ALL标识符，每个UNION ALL分支的标识符列应包含不同的常量值；

CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST ON COMMIT AS
    SELECT C.ID CID, C.NAME CNAME, B.ID BID, B.NAME BNAME, A.NUM,
    A.ROWID AROWID, B.ROWID BROWID, C.ROWID CROWID,'a'
    FROM A, B, C WHERE A.BID = B.ID AND A.CID = C.ID
    UNION ALL 
    SELECT C.ID CID, C.NAME CNAME, B.ID BID, B.NAME BNAME, A.NUM,
    A.ROWID AROWID, B.ROWID BROWID, C.ROWID CROWID,'b'
    FROM A, B, C WHERE A.BID = B.ID AND A.CID = C.ID ;

2.注意在对表进行truncate操作后,或者view log 中的列被删除时

--物化视图日志并不会被清空,此时其会阻塞基表的dml语句,此时必需手动进行COMPLETE刷新物化视图,切记切记
begin
  dbms_mview.refresh(list => 'RUDY_MV',method => 'C');
end;

更多知识有一篇很的博客

http://www.dbabeta.com/2009/mview_step_by_step_01.html