全表提交
增量刷新 一天提交一次
必须创建log 而且 要使用rowid
增量 一分钟刷一次
--注意,我们的基表必须要有主键. create table B (clsid int primary key, name varchar2(10)); create table A (id int primary key, name varchar2(10), clsid int, constraint fk_cls_id foreign key (clsid) references B(clsid)); insert into b values(1,'一班'); insert into b values(2,'二班'); insert into a values(1, '张三', 1); insert into a values(2, '李四', 1); insert into a values(3, '王五', 2); commit; --语法如下: DROP MATERIALIZED VIEW V_AB; CREATE MATERIALIZED VIEW V_AB REFRESH FORCE ON COMMIT AS SELECT A.ID, A.NAME, B.CLSID, B.NAME AS CLSNAME FROM A,B WHERE A.CLSID =B.CLSID; SELECT * FROM V_AB; SELECT * FROM USER_TABLES; DROP MATERIALIZED VIEW MV_AB; CREATE MATERIALIZED VIEW MV_AB REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE+1 AS SELECT A.ID, A.NAME, B.CLSID, B.NAME AS CLSNAME FROM A,B WHERE A.CLSID =B.CLSID; SELECT * FROM MV_AB; UPDATE A SET NAME = '张四' where id = 1; commit; --语法如下: CREATE MATERIALIZED VIEW LOG ON A WITH ROWID; CREATE MATERIALIZED VIEW LOG ON B WITH ROWID; CREATE MATERIALIZED VIEW MV_AB2 REFRESH FAST ON DEMAND START WITH SYSDATE NEXT SYSDATE+1/1440 AS SELECT A.ROWID AS AROWID, B.ROWID AS BROWID, A.ID, A.NAME, B.CLSID, B.NAME AS CLSNAME FROM A,B WHERE A.CLSID=B.CLSID; SELECT * FROM MV_AB2; UPDATE A SET NAME = '张三' where id = 1;commit;