全表提交
增量刷新 一天提交一次
必须创建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;