Home » SQL & PL/SQL » SQL & PL/SQL » MV Refresh On Commit (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
MV Refresh On Commit [message #676391] Thu, 06 June 2019 07:03 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi

We have one materialized view (ABC) which was configured to be refreshed on DEMAND.This ABC will be refreshed at the end of the day by using one procedure.There is one more count MV (ABC_COUNT) which configured to be refresh ON COMMIT;


When the refresh was happening,first its deleting the total data from MV and re populating the fresh data.


There are some others jobs are executing the at the same time which depends on the same MV few times it leads to missing of some records.

In order to resolve this issue I want to depend on the ABC_COUNT mv to get the exact count.

Here my question was what will be the result of the ABC_COUNT mv when the actual ABC mv refresh was happening?

Is there any change to get the ZERO count just like ZERO results in MV ?



Re: MV Refresh On Commit [message #676511 is a reply to message #676391] Thu, 13 June 2019 07:37 Go to previous messageGo to next message
Bill B
Messages: 1909
Registered: December 2004
Senior Member
Having a refresh do a truncate or a delete/insert/commit sequence is easy to do.

To refresh your Mview of ABC the following procedure would do a truncate
DBMS_SNAPSHOT.REFRESH (list => 'ABC', method => 'C',atomic_refresh => false);

To do a refresh that will delete/inset/commit so the table is never empty them do
DBMS_SNAPSHOT.REFRESH (list => 'ABC', method => 'C',atomic_refresh => true);

There is NO need for your count mview

[Updated on: Thu, 13 June 2019 07:38]

Report message to a moderator

Re: MV Refresh On Commit [message #676768 is a reply to message #676511] Mon, 15 July 2019 00:39 Go to previous message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Bill

Thanks for your inputs.This problem has been resolved with different solution.
Even topics are different both are for the same issue.

http://www.orafaq.com/forum/t/205864/

Previous Topic: Returning into clause for insert selectstatements
Next Topic: Sending table data in HTML format through PL/SQL
Goto Forum:
  


Current Time: Tue Nov 19 09:47:41 CST 2019