物化视图学习笔记

物化视图
删除表后物化视图日志自动删除
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-746373/