Oracle物化视图没有刷新

参考

为支持物化视图快速刷新，需要为物化视图用到的每一张表创建视图日志

--创建PO_REC物化视图日志
CREATE MATERIALIZED VIEW LOG ON PO_REC
WITH ROWID, PRIMARY KEY, SEQUENCE(
SUP_ID, AU_ID, APP_SOURCE, STATUS, RECEIPT_CODE, GL_DATE, GL_STATUS,
ORDER_ID,STORE_CODE,STORE_NAME,RECEIPT_VAT_AMOUNT,MATCHED_NET_AMOUNT,
RECEIPT_NET_AMOUNT, 
RECEIPT_TOT_AMOUNT, 
RECEIPT_DISC_NET_AMOUNT, 
RECEIPT_DISC_TOT_AMOUNT, 
PAYPLAN_NET_AMOUNT, 
PAYPLAN_TOT_AMOUNT, 
PAYPLAN_DISC_NET_AMOUNT, 
PAYPLAN_DISC_TOT_AMOUNT)
INCLUDING NEW VALUES;

--创建SUPPLIER物化视图日志
CREATE MATERIALIZED VIEW LOG ON SUPPLIER
WITH ROWID, SEQUENCE(ID, SUP_NO, COMPANY_NAME)
INCLUDING NEW VALUES;
--创建ACCOUNT_UNIT物化视图日志
CREATE MATERIALIZED VIEW LOG ON ACCOUNT_UNIT
WITH ROWID, SEQUENCE(ID, COMPANY_ID, AU_CODE, AU_DESC)
INCLUDING NEW VALUES;
--创建COMPANY物化视图日志
CREATE MATERIALIZED VIEW LOG ON COMPANY 
WITH ROWID, SEQUENCE(ID, COMP_NO, COMP_NAME)
INCLUDING NEW VALUES;

创建物化视图

CREATE MATERIALIZED VIEW MV_PO_REC_BALANCE_AGE 
BUILD IMMEDIATE 
REFRESH FAST 
ON COMMIT 
AS 
SELECT 
e.company_id AS ageCompIdLable,
d.comp_no AS ageCompNoLable,
d.comp_name AS ageCompNameLable,
a.au_id AS ageAuIdLable,
e.au_code AS ageAuNoLable,
e.au_desc AS ageAuNameLable,
a.sup_id AS ageSupIdLable,
a.app_source AS ageAppSource,
c.sup_no AS ageSupNoLable,
c.company_name AS ageSupNameLable,
sum(nvl(a.receipt_net_amount, 0) - nvl(a.payplan_net_amount, 0)) AS ageProvisAmountLable, 
sum(nvl(a.receipt_tot_amount, 0) - nvl(a.payplan_tot_amount, 0)) AS ageProvisTotAmountLable, 
sum(nvl(a.receipt_disc_net_amount, 0) - nvl(a.payplan_disc_net_amount, 0)) AS receiptDiscNetAmount,
sum(nvl(a.receipt_disc_tot_amount, 0) - nvl(a.payplan_disc_tot_amount, 0)) AS receiptDiscTotAmount,
FROM PO_REC a
JOIN SUPPLIER c ON a.sup_id = c.id
JOIN ACCOUNT_UNIT e ON a.au_id = e.id
JOIN COMPANY d ON e.company_id = d.id
where a.status IN (1, 2)
group by (a.app_source, a.sup_id, a.au_id, e.company_id,d.comp_no,d.comp_name,e.au_code,e.au_desc,c.sup_no,c.company_name );

使用物化视图，提前将需要分组计算的金额统计好，数据量从原来的千万级降到了万级，查询速度从原来的几十分钟降到3S，但是上面的视图存在以下问题：

• 当PO_REC表的数据被UPDATE，视图没有快速刷新

最后在参考了这里后，发现对分组求和的物化视图，需要添加count(列名)以及count(*).

修改视图为：

CREATE MATERIALIZED VIEW MV_PO_REC_BALANCE_AGE 
BUILD IMMEDIATE 
REFRESH FAST 
ON COMMIT 
AS 
SELECT 
e.company_id AS ageCompIdLable,
d.comp_no AS ageCompNoLable,
d.comp_name AS ageCompNameLable,
a.au_id AS ageAuIdLable,
e.au_code AS ageAuNoLable,
e.au_desc AS ageAuNameLable,
a.sup_id AS ageSupIdLable,
a.app_source AS ageAppSource,
c.sup_no AS ageSupNoLable,
c.company_name AS ageSupNameLable,
sum(nvl(a.receipt_net_amount, 0) - nvl(a.payplan_net_amount, 0)) AS ageProvisAmountLable, count(nvl(a.receipt_net_amount, 0) - nvl(a.payplan_net_amount, 0)),
sum(nvl(a.receipt_tot_amount, 0) - nvl(a.payplan_tot_amount, 0)) AS ageProvisTotAmountLable, count(nvl(a.receipt_tot_amount, 0) - nvl(a.payplan_tot_amount, 0)),
sum(nvl(a.receipt_disc_net_amount, 0) - nvl(a.payplan_disc_net_amount, 0)) AS receiptDiscNetAmount, count(nvl(a.receipt_disc_net_amount, 0) - nvl(a.payplan_disc_net_amount, 0)),
sum(nvl(a.receipt_disc_tot_amount, 0) - nvl(a.payplan_disc_tot_amount, 0)) AS receiptDiscTotAmount, count(nvl(a.receipt_disc_tot_amount, 0) - nvl(a.payplan_disc_tot_amount, 0)), count(*) 
FROM PO_REC a
JOIN SUPPLIER c ON a.sup_id = c.id
JOIN ACCOUNT_UNIT e ON a.au_id = e.id
JOIN COMPANY d ON e.company_id = d.id
where a.status IN (1, 2)
group by (a.app_source, a.sup_id, a.au_id, e.company_id,d.comp_no,d.comp_name,e.au_code,e.au_desc,c.sup_no,c.company_name );

删除物化视图以及视图LOG

--删除物化视图MV_PO_REC
DROP MATERIALIZED VIEW MV_PO_REC;

--删除PO_REC表上的物化视图LOG
DROP MATERIALIZED VIEW LOG ON PO_REC;