• 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;
    

      

  • 相关阅读:
    用了7年做到项目经理,女朋友却离开了我
    手把手教你建网站--程序小白适用篇
    燃尽图的学习与理解
    每周进度
    四人组队
    读构建之法之感
    两人组队,小学生的四则运算
    词汇统计
    AMQP中的架构和组成元素
    MSSQL机制
  • 原文地址:https://www.cnblogs.com/eadela/p/11563209.html
Copyright © 2020-2023  润新知