【物化视图】使用可更新的物化视图。

创建物化视图是默认是不可更新的，要创建可以更新的物化视图必须加上 for update 关键字。
yang@rac1>create table t (id number ,name varchar2(30),val number);
Table created.
yang@rac1>create materialized view log on t with rowid,sequence (id,name) including  new values;
Materialized view log created.
yang@rac1>create materialized view mv_t_id refresh fast as select id ,count(1) from t group by id;
Materialized view created.
yang@rac1>insert into t values(1,'a',1);
1 row created.
yang@rac1>insert into t values(2,'b',2);
1 row created.
yang@rac1>insert into t values(3,'lily',3);
1 row created.
yang@rac1>insert into t values(4,'yang',4);
1 row created.
yang@rac1>select * from mv_t_id;

        ID   COUNT(1)
---------- ----------
         1          1
         2          1
         3          1
         5          1
         6          2
向物化视图中插入数据。
yang@rac1>insert into mv_t_id values (7,1);
insert into mv_t_id values (7,1)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
-删除物化日志之后再次尝试插入，仍然报错。
yang@rac1>drop materialized view log on t;
Materialized view log dropped.

yang@rac1>insert into mv_t_id values (7,1);
insert into mv_t_id values (7,1)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
创建可更新的物化视图：
建立测试表并创建物化日志。
yang@rac1>create table t as select object_id id ,object_name name from user_objects;
Table created.
yang@rac1>create materialized view log on t with rowid;
Materialized view log created.
                 
yang@rac1>create materialized view mv_t refresh fast with rowid for update as
  2  select * from t;
Materialized view created.
yang@rac1>select * from mv_t;

        ID NAME
---------- ---------------
    130864 YANG_SEQ
    132031 YANG_A
    132032 SYS_C0066382
    132033 YANG_B
    132034 SYS_C0066383
    132035 FACT
    132036 MLOG$_YANG_A
    132037 MLOG$_YANG_B
    132038 MLOG$_FACT
    132039 T
    131949 YANG_ROWID
    131951 YANG_PK
    131952 SYS_C0066303
    131955 YANG_OBJECT
    131956 YANG_OID
    131957 SYS_C0066304
    132018 YANG_C
    132017 MV_CAPABILITIES_TABLE
    132030 MLOG$_YANG_PK
    132027 MLOG$_YANG_ROWID
           LINKORACL
           LINKYANG

22 rows selected.
yang@rac1>insert into mv_t values (1,'yang');
1 row created.
yang@rac1>select * from mv_t;

        ID NAME
---------- ---------------
    130864 YANG_SEQ
    132031 YANG_A
    132032 SYS_C0066382
    132033 YANG_B
    132034 SYS_C0066383
    132035 FACT
    132036 MLOG$_YANG_A
    132037 MLOG$_YANG_B
    132038 MLOG$_FACT
    132039 T
    131949 YANG_ROWID
    131951 YANG_PK
    131952 SYS_C0066303
    131955 YANG_OBJECT
    131956 YANG_OID
    131957 SYS_C0066304
    132018 YANG_C
    132017 MV_CAPABILITIES_TABLE
    132030 MLOG$_YANG_PK
    132027 MLOG$_YANG_ROWID
           LINKORACL
           LINKYANG
          1 yang
23 rows selected.
查看物化日志，没有关于更新的记录。
yang@rac1>select * from mlog$_t;
no rows selected
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-692357/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-692357/