oracle–物化视图

• 2019 年 10 月 10 日
• 笔记

全表提交

增量刷新 一天提交一次

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