About Materialized View（物化视图）

Materialized View（物化视图）

实践案例

select * from DWD_FACTORY t;

CREATE TABLE CEC_FACTORY_TEST
AS 
SELECT * FROM DWD_FACTORY;
--创建唯一索引
--CREATE UNIQUE INDEX UIDX_CEC_FACTORY_TEST ON CEC_FACTORY_TEST(FACTORY) TABLESPACE edw_com_idx;
--创建表主键约束
--ALTER TABLE CEC_FACTORY_TEST ADD (CONSTRAINT PK_CEC_FACTORY_TEST PRIMARY KEY(FACTORY));
--建立MV日志，单一聚合视图的快速刷新需要指定INCLUDING NEW VALUES子句
CREATE MATERIALIZED VIEW LOG ON CEC_FACTORY_TEST INCLUDING NEW VALUES;
--赋用户创建物化视图的权限
--GRANT CREATE MATERIALIZED VIEW TO EDBADM; 
--创建物化视图
CREATE MATERIALIZED VIEW mv_CEC_FACTORY_TEST BUILD IMMEDIATE REFRESH FAST 
START WITH to_date('2019-05-30 08:00:00','yyyy-mm-dd hh24:mi:ss')
NEXT SYSDATE+1/24
AS SELECT * FROM CEC_FACTORY_TEST;
--建立trriger测试表
CREATE TABLE mv_CEC_FACTORY_TEST_trigger AS 
SELECT * FROM CEC_FACTORY_TEST;

ALTER TABLE CEC_FACTORY_TEST ADD (DML_FLAG VARCHAR2(8));
--ALTER TABLE CEC_FACTORY_TEST DROP COLUMN  DML_FLAG;
ALTER TABLE mv_CEC_FACTORY_TEST_trigger ADD (DML_FLAG VARCHAR2(8));

--创建源表上的Trriger
CREATE OR  REPLACE TRIGGER Tri_CEC_FACTORY_TEST
AFTER INSERT OR UPDATE OR DELETE ON CEC_FACTORY_TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
  BEGIN
    CASE 
      WHEN inserting THEN 
        INSERT INTO mv_CEC_FACTORY_TEST_trigger 
        VALUES ( :NEW.site,
                 :NEW.factory,
                 :NEW.description,
                 :NEW.code,
                 :NEW.object_rrn,
                 :NEW.org_rrn,
                 :NEW.is_active,
                 :NEW.interface_time
                 ,'insert'
        );
       WHEN updating THEN 
        INSERT INTO mv_CEC_FACTORY_TEST_trigger 
        VALUES ( :NEW.site,
                 :NEW.factory,
                 :NEW.description,
                 :NEW.code,
                 :NEW.object_rrn,
                 :NEW.org_rrn,
                 :NEW.is_active,
                 :NEW.interface_time
                 ,'update'
        );
       WHEN deleting THEN 
        INSERT INTO mv_CEC_FACTORY_TEST_trigger 
        VALUES ( :OLD.site,
                 :OLD.factory,
                 :OLD.description,
                 :OLD.code,
                 :OLD.object_rrn,
                 :OLD.org_rrn,
                 :OLD.is_active,
                 :OLD.interface_time
                 ,'delete'
        );
      END CASE;
    EXCEPTION 
      WHEN OTHERS THEN
        RAISE;
  END Tri_CEC_FACTORY_TEST;
        
        
--对源表进行操作
SELECT * FROM CEC_FACTORY_TEST;    
   
DELETE FROM CEC_FACTORY_TEST WHERE FACTORY IN ('ARRAY','CF');
COMMIT;     
TRUNCATE TABLE CEC_FACTORY_TEST;
INSERT INTO CEC_FACTORY_TEST SELECT * FROM DWD_FACTORY;
COMMIT;
UPDATE CEC_FACTORY_TEST SET DESCRIPTION='阵列(TFT)'WHERE FACTORY='ARRAY';
COMMIT;
UPDATE CEC_FACTORY_TEST SET DESCRIPTION='彩膜(CF)'WHERE FACTORY='CF';
COMMIT;
UPDATE CEC_FACTORY_TEST SET DESCRIPTION='成盒(Cell)'WHERE FACTORY='CELL';
COMMIT;
UPDATE CEC_FACTORY_TEST SET DESCRIPTION='模组(SL)'WHERE FACTORY='SL';
COMMIT;
UPDATE CEC_FACTORY_TEST SET DESCRIPTION='模组(OC)'WHERE FACTORY='OC';
COMMIT;

SELECT * FROM CEC_FACTORY_TEST;
--手动刷新 EXEC
CALL dbms_mview.refresh('mv_CEC_FACTORY_TEST');
--查看物化视图
SELECT * FROM mlog$_CEC_FACTORY_TEST;

SELECT * FROM MV_CEC_FACTORY_TEST_TRIGGER;