oracle--物化视图

增量刷新 一天提交一次

必须创建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;