ITPub博客

首页 > 数据库 > Oracle > 物化视图学习笔记

物化视图学习笔记

原创 Oracle 作者:jeanron100 时间:2012-10-14 11:02:59 0 删除 编辑
物化视图
删除表后物化视图日志自动删除
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<100;
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<3;
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<2;
update mv1 set object_id=1000 where rownum<2
       *
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.

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

请登录后发表评论 登录
全部评论
技术文章每天更新,阵地已转移到微信公众号端。 公众号:jianrong-notes

注册时间:2012-05-14

  • 博文量
    1498
  • 访问量
    14268619