ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【物化视图】使用可更新的物化视图。

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

原创 Linux操作系统 作者:杨奇龙 时间:2011-04-12 20:47:44 0 删除 编辑
创建物化视图是默认是不可更新的,要创建可以更新的物化视图必须加上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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7841732