Home » SQL & PL/SQL » SQL & PL/SQL » Materialised Views - FAST Refresh (Oracle 11g)
Materialised Views - FAST Refresh [message #669841] |
Wed, 16 May 2018 05:51 |
chat2raj.s
Messages: 163 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
Can someone kindly share good manual (Writeup/Video link) about Materialized View FAST Refresh Option. I could find so many limitations for this and so couldn't successfully create a Fast Refresh Materialized View as required.
Thanks
|
|
|
|
Re: Materialised Views - FAST Refresh [message #669845 is a reply to message #669842] |
Wed, 16 May 2018 07:07 |
chat2raj.s
Messages: 163 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
CREATE MATERIALIZED VIEW OV_MGT_REP_MV
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH ROWID
AS
SELECT od_comp_code,
od_doc_txn_code "INV_TXN",
od_doc_no "INV_NO",
od_doc_dt "INV_DT",
cust_name,
od_ig_code "ITEM_GROUP",
od_item_code "ITEM_CODE",
od_item_desc "ITEM_DESC",
od_uom_code "ITEM_UOM",
Sum(qty) delivered_qty,
c.invh_type,
c.invh_sys_id,
a.invi_sys_id,
A.ROWID AID,
B.ROWID BID,
C.ROWID CID,
D.ROWID DID,
E.ROWID EID
FROM os_operations_detail@LIVE b,
ot_invoice_head@LIVE c,
ot_invoice_item@LIVE a,
om_customer@LIVE d,
om_item_uom@LIVE e,
OV_INV_DEL_INFO_MIS
WHERE od_doc_h_sys_id = c.invh_sys_id
AND od_doc_i_sys_id = a.invi_sys_id
AND a.invi_invh_sys_id = c.invh_sys_id
AND c.invh_type IN (1, 3)
AND cust_code = invh_cust_code
AND invh_appr_status = 3
AND iu_item_code = od_item_code
AND iu_uom_code = invi_uom_code
AND Nvl(a.invi_ref_invi_sys_id, a.invi_sys_id) = dn_sys_id
AND od_doc_no IN (2018001059, 2017300030)
GROUP BY od_comp_code,
od_doc_txn_code,
od_doc_no,
od_doc_dt,
cust_name,
od_ig_code ,
od_item_code ,
od_item_desc ,
od_uom_code,
od_qty_bu ,
od_gross_sales_lc_val_1,od_lc_h_disc_val_1,iu_max_loose_1,od_lc_i_disc_val_1,c.invh_type,c.invh_sys_id,
a.invi_sys_id, A.ROWID ,
B.ROWID ,
C.ROWID ,
D.ROWID ,
E.ROWID;
Error : ORA-12015: cannot create a fast refresh materialized view from a complex query
All the underlying tables already have materialized view logs.
OV_INV_DEL_INFO_MIS is another materialized view used here.
|
|
|
|
|
|
Re: Materialised Views - FAST Refresh [message #669850 is a reply to message #669849] |
Wed, 16 May 2018 12:44 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Typically aggregates will not allow fast refresh to occur. If you are grouping on rowid you are not summing your rows any way. Get rid of the SUM and the group by, then you can query the mview with a sum and it will return very fast, assuming that you have the proper indexes. You also want to index all the ROWID's so that the refresh can find any rows that it needs to modify.
CREATE MATERIALIZED VIEW OV_MGT_REP_MV
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH ROWID
AS
SELECT od_comp_code,
od_doc_txn_code "INV_TXN",
od_doc_no "INV_NO",
od_doc_dt "INV_DT",
cust_name,
od_ig_code "ITEM_GROUP",
od_item_code "ITEM_CODE",
od_item_desc "ITEM_DESC",
od_uom_code "ITEM_UOM",
qty delivered_qty,
c.invh_type,
c.invh_sys_id,
a.invi_sys_id,
A.ROWID AID,
B.ROWID BID,
C.ROWID CID,
D.ROWID DID,
E.ROWID EID
FROM os_operations_detail@LIVE b,
ot_invoice_head@LIVE c,
ot_invoice_item@LIVE a,
om_customer@LIVE d,
om_item_uom@LIVE e,
OV_INV_DEL_INFO_MIS
WHERE od_doc_h_sys_id = c.invh_sys_id
AND od_doc_i_sys_id = a.invi_sys_id
AND a.invi_invh_sys_id = c.invh_sys_id
AND c.invh_type IN (1, 3)
AND cust_code = invh_cust_code
AND invh_appr_status = 3
AND iu_item_code = od_item_code
AND iu_uom_code = invi_uom_code
AND Nvl(a.invi_ref_invi_sys_id, a.invi_sys_id) = dn_sys_id
AND od_doc_no IN (2018001059, 2017300030);
[Updated on: Wed, 16 May 2018 12:46] Report message to a moderator
|
|
|
|
|
Re: Materialised Views - FAST Refresh [message #673626 is a reply to message #669854] |
Tue, 27 November 2018 23:42 |
chat2raj.s
Messages: 163 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
I was able to create a materialized view based on a complex view instead of using the query directly to create MV. I solved my problem as of now, but need to work more on it to understand and solve it better.
|
|
|
Goto Forum:
Current Time: Fri Sep 27 11:16:17 CDT 2024
|