Oracle物理视图定时刷新

最新推荐文章于 2024-05-10 15:38:14 发布

崇文馆居士

最新推荐文章于 2024-05-10 15:38:14 发布

阅读量986

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/qq_34677276/article/details/107557969

版权

笔记专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Oracle做的物理视图（MATERIALIZED VIEW），每天定时刷新，用户查询时候，快速查出，不让用户等待。

CREATE MATERIALIZED VIEW MV_MTL_SLUGGISH_SURPLUS
REFRESH COMPLETE ON DEMAND
START WITH TO_DATE('25-07-2020 07:20:00', 'DD-MM-YYYY HH24:MI:SS') NEXT TO_DATE(CONCAT(TO_CHAR(SYSDATE + 1,'DD-MM-YYYY'),' 07:20:00'),'DD-MM-YYYY HH24:MI:SS') 
AS
SELECT t1.img01
,(tc_stb07) unit_price
,(t1.C_0_30)*(tc_stb07+tc_stb08+tc_stb09) amount_0_30
,t1.C_31_60*(tc_stb07+tc_stb08+tc_stb09) amount_31_60
,t1.C_61_90*(tc_stb07+tc_stb08+tc_stb09) amount_61_90
,t1.C_91_180*(tc_stb07+tc_stb08+tc_stb09) amount_91_180
,t1.C_181_270*(tc_stb07+tc_stb08+tc_stb09) amount_181_270
,t1.C_271_365*(tc_stb07+tc_stb08+tc_stb09) amount_271_365
,t1.C_366*(tc_stb07+tc_stb08+tc_stb09) amount_366
,t2.mss08*(tc_stb07+tc_stb08+tc_stb09) "預計結存金額"
,t3.surplus*(tc_stb07+tc_stb08+tc_stb09) "不計未收貨結存金額"
,MTL_FG.fg
,mtl_cust.cust customer
,t6.gen02 buyer
,t7.gen02 planner
 from (
SELECT NVL(img01,tc_aag001) img01,
nvl(C_0_30,0)+NVL(TC_AAG_qty,0) C_0_30,C_31_60,C_61_90,
C_91_180,C_181_270,C_271_365,C_366 FROM
(select img01
,sum(case when ckw.get_forward_date(0)-img15 between 0 and 30 then img10 end) "C_0_30"
,sum(case when ckw.get_forward_date(0)-img15 between 31 and 60 then img10 end) "C_31_60"
,sum(case when ckw.get_forward_date(0)-img15 between 61 and 90 then img10 end) "C_61_90"
,sum(case when ckw.get_forward_date(0)-img15 between 91 and 180 then img10 end) "C_91_180"
,sum(case when ckw.get_forward_date(0)-img15 between 181 and 270 then img10 end) "C_181_270"
,sum(case when ckw.get_forward_date(0)-img15 between 271 and 365 then img10 end) "C_271_365"
,sum(case when ckw.get_forward_date(0)-img15 between 366 and 99999 then img10 end) "C_366"
from ckw.img_file LEFT JOIN ckw.ima_file ON img01=ima01
where img10>0
--and img01=ima01
and ima08='P'
group by img01) t4 FULL JOIN
(SELECT TC_AAG001,SUM(TC_AAG011+TC_AAG012+TC_AAG013+TC_AAG014) TC_AAG_qty
FROM ckw.tc_aag_file
WHERE TC_AAG002 LIKE 'WP%' AND TC_AAG003='P'
GROUP BY TC_AAG001) t5 ON t4.img01=t5.TC_AAG001
) t1
LEFT JOIN ckw.mv_purchase_mtl_mrp_surplus t2 ON t1.img01=t2.mss01
LEFT JOIN ckw.mv_purchase_mtl_stock_surplus t3 ON t1.img01=t3.mss01
LEFT JOIN (SELECT * FROM ckw.tc_stb_file WHERE TC_STB02=to_char(sysdate,'yyyy')
            AND tc_stb03=0) ON img01=tc_stb01
LEFT JOIN ckw.MTL_FG ON t1.img01=MTL_FG.mtl
LEFT JOIN ckw.mtl_cust ON t1.img01=mtl_cust.mtl
LEFT JOIN ckw.ima_file ON t1.img01=ima_file.ima01
LEFT JOIN ckw.gen_file t6 ON ima_file.ima43=t6.gen01
LEFT JOIN ckw.gen_file t7 ON ima_file.ima67=t7.gen01;