Oracle ~ 创建物化视图

注意：物化视图中不能用子查询，因此如果涉及子查询，需要先把sql写到普通视图，然后再将普通视图写入物化视图

创建普通视图，因为物化视图不能有子查询

CREATE OR REPLACE VIEW AISKWAR.Eco_war_total_VIEW
as 
(
SELECT t.*
  , (
    SELECT COUNT(DISTINCT list_type || list_date)
    FROM aiskwar.war_fcm_clientmatchinfo wfm
    WHERE wfm.cust_id = t.cust_id
  ) AS jkCount
FROM (

  SELECT 
    wmr.id AS risk_id, 
    wmr.oc_date AS occu_date,
    COUNT(*) AS yjs, 
    wmr.isdealcomment AS isn_deal
    , vs.special_name AS chn_serv
    , CASE 
        WHEN ac.srv_branch_name IS NULL THEN ac.branch_name
        ELSE ac.srv_branch_name
      END AS zfwyyb
    , CASE 
      WHEN ac.srv_up_branch_name IS NULL THEN ac.up_branch_name
      ELSE ac.srv_up_branch_name
    END AS zfwfgs
    , wmr.client_id AS cust_id, ac.client_name, sy.dict_prompt AS acc_type, wmr.account_group AS acc_grp
    , wmr.stock_code AS scr_cd, wmr.stock_name, hs.dict_name AS exch_mkt, sc.stock_type
    , aiskcon.f_get_group_name('2', ' ', wmr.stock_code, wmr.exchange_type, wmr.oc_date) AS scr_grp
    , wmr.risk_type AS risk_name, wmr.risk_lvl, ac.srv_up_branch_name, ac.srv_branch_name, ac.up_branch_name
    , ac.branch_name, ac.organ_flag
  FROM aiskwar.WAR_MONITOR_BRK wmr
  LEFT JOIN aiskbus.stkcode sc ON substr(wmr.stock_account, 1, instr(wmr.stock_account, '.', -1) - 1) = sc.stock_code
      AND wmr.exchange_type = sc.exchange_type 
  LEFT JOIN aiskbus.aisk_client ac ON to_char(wmr.client_id) = ac.client_id 
  LEFT JOIN aisksys.sys_dictionary hs ON hs.dict_cd = wmr.exchange_type
      AND hs.dict_entr = 'AI_1003' 
  LEFT JOIN aiskbus.vact_specialaccount vs ON vs.client_id = wmr.client_id 
  LEFT JOIN AISKBUS.SYSDICTIONARY sy ON sy.dict_entry = '1048'
      AND sy.subentry = ac.organ_flag 
  GROUP BY
    wmr.id, wmr.oc_date, wmr.isdealcomment, vs.special_name, wmr.client_id, ac.client_name, sy.dict_prompt, wmr.account_group, wmr.stock_code, wmr.stock_name, hs.dict_name, sc.stock_type, aiskcon.f_get_group_name('2', ' ', wmr.stock_code, wmr.exchange_type, wmr.oc_date), wmr.risk_type, wmr.risk_lvl, ac.srv_up_branch_name, ac.srv_branch_name, ac.up_branch_name, ac.branch_name, ac.organ_flag
    

) t
WHERE 1 = 1
)

创建物化视图

CREATE MATERIALIZED VIEW AISKWAR.Eco_war_total_MV 
             REFRESH COMPLETE START WITH SYSDATE 
            NEXT TRUNC(SYSDATE+29)+5.5/24
AS 
select * from AISKWAR.Eco_war_total_VIEW

更改物化视图每天运行时间

alter materialized view AISKWAR.Eco_war_total_MV  
refresh force on demand 
start with sysdate 
next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');

查询

select * from AISKWAR.Eco_war_total_VIEW

select * from AISKWAR.Eco_war_total_MV