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

      

  • 相关阅读:
    矩阵价值和
    排列组合问题
    X国的军队
    石子合并加强版
    P1042 乒乓球
    Dinner 点餐
    一文看尽图像分类问题
    [Udemy] Recommender Systems and Deep Learning in Python
    推荐系统学习
    [NLP] 酒店名归类
  • 原文地址:https://www.cnblogs.com/eadela/p/11563209.html
Copyright © 2020-2023  润新知