物化视图
删除表后物化视图日志自动删除
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) |
