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