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 Go to next message
chat2raj.s
Messages: 119
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 #669842 is a reply to message #669841] Wed, 16 May 2018 05:56 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
You will need to post the materialized view creation statement and the materialized view log statements before anyone can assist. Remember to enclose the code within [code] tags.
Re: Materialised Views - FAST Refresh [message #669845 is a reply to message #669842] Wed, 16 May 2018 07:07 Go to previous messageGo to next message
chat2raj.s
Messages: 119
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 #669847 is a reply to message #669845] Wed, 16 May 2018 07:43 Go to previous messageGo to next message
_jum
Messages: 533
Registered: February 2008
Senior Member
Quote:
Can someone kindly share good manual (Writeup/Video link) about Materialized View FAST Refresh Option.
Have a look at: Fast refreshable materialized view errors part one to part seven.

And try your MV functionality with a simple table like EMP first...

[Updated on: Wed, 16 May 2018 07:45]

Report message to a moderator

Re: Materialised Views - FAST Refresh [message #669848 is a reply to message #669845] Wed, 16 May 2018 07:56 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
Quote:
and the materialized view log statements
Are you sure that you want to GROUP BY the rowids?
Re: Materialised Views - FAST Refresh [message #669849 is a reply to message #669845] Wed, 16 May 2018 08:09 Go to previous messageGo to next message
JohnGuru
Messages: 6
Registered: March 2017
Junior Member
Did you read documentation? Analyzing Materialized View Capabilities

SY.
Re: Materialised Views - FAST Refresh [message #669850 is a reply to message #669849] Wed, 16 May 2018 12:44 Go to previous messageGo to next message
Bill B
Messages: 1814
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 #669851 is a reply to message #669850] Wed, 16 May 2018 17:25 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
Close, but no cigar! The rowid from OV_INV_DEL_INFO_MIS needs to be included. And I am prepared to bet the OP has not created an MV log on it, either.
Re: Materialised Views - FAST Refresh [message #669854 is a reply to message #669851] Thu, 17 May 2018 02:18 Go to previous message
chat2raj.s
Messages: 119
Registered: October 2010
Location: Chennai, India
Senior Member
I will go through the documentation link and do changes on my mv and feedback. Thanks.
Previous Topic: Procedure
Next Topic: Help required for adding patition by range in table
Goto Forum:
  


Current Time: Wed Nov 14 19:45:23 CST 2018