物化视图学习笔记-阿里云开发者社区

物化视图学习笔记

2012-10-14 1048

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 物化视图删除表后物化视图日志自动删除 SQL> CREATE MATERIALIZED VIEW LOG ON TT WITH ROWID,SEQUENCE(OBJECT_ID,OBJECT_NAME) INCLUDING NEW VALUES; Materialized view log created.

物化视图
删除表后物化视图日志自动删除
SQL> CREATE MATERIALIZED VIEW LOG ON TT WITH ROWID,SEQUENCE(OBJECT_ID,OBJECT_NAME) INCLUDING NEW VALUES;

Materialized view log created.

SQL> EXEC DBMS_SNAPSHOT.REFRESH('MV1');
BEGIN DBMS_SNAPSHOT.REFRESH('MV1'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "HR"."TT" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

SQL> DROP MATERIALIZED VIEW MV1;

Materialized view dropped. --删除旧的物化视图

SQL> CREATE MATERIALIZED VIEW MV1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT OBJECDT_ID,OBJECT_NAME FROM TT GROUP BY OBJECT_ID,OBJECT_NAME;
CREATE MATERIALIZED VIEW MV1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT OBJECDT_ID,OBJECT_NAME FROM TT GROUP BY OBJECT_ID,OBJECT_NAME
*
ERROR at line 1:
ORA-00904: "OBJECDT_ID": invalid identifier

SQL> C/OBJECDT/OBJECT
1* CREATE MATERIALIZED VIEW MV1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT OBJECT_ID,OBJECT_NAME FROM TT GROUP BY OBJECT_ID,OBJECT_NAME
SQL> /

Materialized view created.

SQL> SELECT COUNT(*) FROM MV1;

COUNT(*)
----------
4258

SQL> INSERT INTO TT SELECT OBJECT_ID+1000,OBJECT_NAME,OBJECT_TYPE FROM TT WHERE ROWNUM

99 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM MV1;

COUNT(*)
----------
4357

SQL> DELETE TT;

4357 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM MV1;

COUNT(*)
----------
4357

SQL> EXEC DBMS_SNAPSHOT.REFRESH('MV1');
BEGIN DBMS_SNAPSHOT.REFRESH('MV1'); END;

*
ERROR at line 1:
ORA-12057: materialized view "HR"."MV1" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

SQL> EXEC DBMS_SNAPSHOT.REFRESH('MV1','C');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM MV1;

COUNT(*)
----------
0
--如果对基表进行删除，修改操作，必须手动进行complete refresh
--insert 操作
SQL> insert into tt select object_id,object_name,object_type from all_objects;

4259 rows created.

SQL> select count(*) from mv1;

COUNT(*)
----------
0

SQL> commit;

Commit complete.

SQL>select count(*) from mv1;

COUNT(*)
----------
      4259
--update 操作
-------------- ------------------------------
          5453 ALL_OUTLINES
          5455 DBA_OUTLINES
          5495 ORA_DICT_OBJ_OWNER
SQL> l
1* update tt set object_id=5453 ,object_name=ALL_OUTLINES where object_id=5455
SQL> update tt set object_id=5453 ,object_name='ALL_OUTLINES' where object_id=5455;

1 row updated.

SQL> select count(*) from mv1;

COUNT(*)
----------
4160

SQL> commit;

Commit complete.

SQL> select count(*) from mv1;

COUNT(*)
----------
4160

SQL> exec dbms_snapshot.refresh('MV1','C');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)FROM MV1;

COUNT(*)
----------
4159

--物化视图日志
SQL> desc mlog$_tt
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_ID                                          NUMBER
OBJECT_NAME                                        VARCHAR2(30)
M_ROW$$                                            VARCHAR2(255)
SEQUENCE$$                                         NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)

SQL> select * from mlog$_tt;

no rows selected

--insert操作，未commit时
SQL> insert into tt select object_id+1001,object_name,object_type from tt where rownum

2 rows created.

SQL> select count(*) from mlog$_tt;

COUNT(*)
----------
         2
OBJECT_ID OBJECT_NAME          M_ROW$$              SEQUENCE$$ SNAPTIME$ D O CHANGE_VECTOR$$
---------- -------------------- -------------------- ---------- --------- - - --------------------
      7027 WPG_DOCLOAD          AAAD/3AAFAAAACPAAA        37102 01-JAN-00 I N FE
      7028 DBMS_DEBUG_JDWP      AAAD/3AAFAAAACPAAB        37103 01-JAN-00 I N FE
SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_tt;

COUNT(*)
----------
0

--可更新物化视图
SQL> update mv1 set object_id=1000 where rownum update mv1 set object_id=1000 where rownum *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> truncate table mv1;

Table truncated.

SQL> create materialized view mv1 refresh fast on commit enable query rewrite as select * from tt;
create materialized view mv1 refresh fast on commit enable query rewrite as select * from tt
*
ERROR at line 1:
ORA-12014: table 'TT' does not contain a primary key constraint

SQL> create materialized view mv1 refresh fast on commit with rowid enable query rewrite as select * from tt;

Materialized view created.

SQL> create materialized view mv1 for update as select object_id,object_name from tt group by object_id,object_name;
create materialized view mv1 for update as select object_id,object_name from tt group by object_id,object_name
*
ERROR at line 1:
ORA-12013: updatable materialized views must be simple enough to do fast refresh

SQL> create materialized view mv1 refresh fast with rowid for update enable query rewrite as select * from tt;

Materialized view created.

阅读(8090) | 评论(0) | 转发(3) |

上一篇：关于drop操作对role的影响

下一篇：foreign key的一些总结