oracle-物化视图-MATERIALIZED VIEW 物化视图学习

----建测试表

create table userA.test20210730 as select * from userA.sales where rownum<10000000;

----创建物化视图日志

CREATE MATERIALIZED VIEW LOG ON test20210730
WITH ROWID, SEQUENCE(STORE_CODE,
            PRODUCT_SKU_CODE,
            SECOND_AREA_NAME,
            SECOND_AREA_MANAGER_EMNAME,
            AMIBA_MANAGER_EMNAME,
            SUPERVISE_MANAGER_EMNAME,
           GUID_EMNAME,
            EMPLOYEE_NAME,
            report_date) INCLUDING NEW VALUES;
            

-----创建物化视图

CREATE MATERIALIZED VIEW userA.test20210730_mv
refresh fast on demand
with rowid
START  WITH SYSDATE NEXT SYSDATE + 2/60*24
AS
     SELECT f.STORE_CODE,
            f.PRODUCT_SKU_CODE,
            f.SECOND_AREA_NAME,
            f.SECOND_AREA_MANAGER_EMNAME,
            f.AMIBA_MANAGER_EMNAME,
            f.SUPERVISE_MANAGER_EMNAME,
            f.GUID_EMNAME,
            f.EMPLOYEE_NAME,
            f.report_date
       FROM userA.test20210730 f
   GROUP BY f.STORE_CODE,
            f.PRODUCT_SKU_CODE,
            f.SECOND_AREA_NAME,
            f.SECOND_AREA_MANAGER_EMNAME,
            f.AMIBA_MANAGER_EMNAME,
            f.SUPERVISE_MANAGER_EMNAME,
            f.GUID_EMNAME,
            f.EMPLOYEE_NAME,
            f.report_date
   ORDER BY f.STORE_CODE, f.PRODUCT_SKU_CODE;

----查看物化视图日志的内容。

select * from userA.mlog$_test20210730;

select count(*) from     MLOG$_TEST20210730;

----查看物化视图

select count(*) from test20210730_mv;

----完全刷新

EXEC DBMS_MVIEW.REFRESH('test20210730_mv','c');

----快速刷新

EXEC DBMS_MVIEW.REFRESH('test20210730_mv','f');

----查看物化视图状态信息

SELECT * FROM USER_MVIEWS 
    WHERE MVIEW_NAME = 'TEST20210730_MV';
    
 select owner,mview_name,refresh_method,last_refresh_date,compile_state from user_mviews; 

----向测试表插入数据

 insert into userA.test20210730  select * from  userA.sales where rownum<1000;
 commit;

----删除物化视图日志和物化视图

drop materialized view log on test20210730; 
DROP MATERIALIZED VIEW TEST20210730_MV;

ALTER MATERIALIZED VIEW userA.TEST20210730_MV 
REFRESH FORCE ON DEMAND 
START WITH SYSDATE NEXT SYSDATE + 2/60*24;

----问题 ：
设置的每2分钟自动刷新没成功。