oracle 物化视图

CREATE MATERIALIZED VIEW MV_PVBDP_STATISTICS_ALERT
REFRESH FORCE ON DEMAND
START WITH TO_DATE('18-01-2017 17:00:00','DD-MM-YYYY HH24:MI:SS') NEXT TRUNC(SYSDATE,'HH24')+1/24 
AS
SELECT B.XZQH,B.ZZJGDM,C.TJRQ,C.YJLX,C.CLZT,COUNT(1) YJSL
          FROM (SELECT T3.YJBH,t3.zdrybh
                   FROM T_PVBDP_ALERT_RELATED T3,(SELECT T1.ZDRYBH,T2.BKBH
                           FROM T_PVBDP_PERSON_COLLECTION T1,T_PVBDP_PERSON_DISPATCHED T2
                          WHERE T1.ZDRYBH = T2.ZDRYBH
                            AND T1.SCBS = '0'
                            and t2.YXX = '1'
                            AND T1.SFZRR = '1'
                            and t2.BKZTDM = '1') T4
                  WHERE T3.BKBH = T4.BKBH
                    AND T3.ZDRYBH = T4.ZDRYBH
                  GROUP BY T3.YJBH,t3.zdrybh,t3.yjlx) a,(select ZRRDWDM zzjgdm,ZRRXZQHID xzqh,zdrybh
                   from t_pvbdp_person_collection) b,(
                 --获取从20170101到当前时间的预警信息
                 select yjlx,clzt,yjbh,t2.daylist tjrq
                   from t_pvbdp_alert t1,(SELECT TO_CHAR(TO_DATE('2017-01-01','yyyy-MM-dd') +
                                         ROWNUM - 1,'yyyyMMdd') as daylist
                            FROM DUAL
                          CONNECT BY ROWNUM <=
                                     trunc(to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') -
                                           to_date('2017-01-01','yyyy-MM-dd')) + 1) t2
                  where substr(t1.YJSJ,1,8) = t2.daylist) c
         where a.zdrybh = b.zdrybh
           and a.yjbh = c.yjbh
         group by b.xzqh,b.zzjgdm,c.yjlx,c.clzt,c.tjrq;