为支持物化视图快速刷新，需要为物化视图用到的每一张表创建视图日志
--创建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;
